Session Starts

-- models/session_starts.sql
{{ config(materialized='view') }}

SELECT
  s.id,
             s.account_id,
             s.created_at,
             v.created_at,
             datediff(min, s.created_at,v.created_at) as "time to start"
            FROM (select id,created_at, account_id, creator_id, study_id from company_orders_production.sessions where state='completed' and created_at>='2015-01-01') s
            JOIN (select id,email from company_orders_production.users where roles&2=0 and email not like '[email protected]' and email!='<% email_placeholder %>') u ON u.id = s.creator_id
            JOIN company_orders_production.studies ON studies.id = s.study_id
            JOIN company_orders_production.accounts ON accounts.id = s.account_id
            join (select product_id, sku_id from company_orders_production.line_items where product_type='Study') li on studies.id=li.product_id
            join company_orders_production.versions v  ON v.item_id = s.id
  WHERE v.object_changes LIKE '%accepted%'
  AND v.object_changes LIKE '%requested%'
            order by s.created_at asc
            LIMIT 300000

# models/schema.yml
version: 2

models:
  - name: session_starts
    columns:
      - name: id
      - name: account_id
      - name: time_to_start
      - name: lessthan1hr

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
  s.id,
             s.account_id,
             s.created_at,
             v.created_at,
             datediff(min, s.created_at,v.created_at) as "time to start"
            FROM (select id,created_at, account_id, creator_id, study_id from company_orders_production.sessions where state='completed' and created_at>='2015-01-01') s
            JOIN (select id,email from company_orders_production.users where roles&2=0 and email not like '[email protected]' and email!='<% email_placeholder %>') u ON u.id = s.creator_id
            JOIN company_orders_production.studies ON studies.id = s.study_id
            JOIN company_orders_production.accounts ON accounts.id = s.account_id
            join (select product_id, sku_id from company_orders_production.line_items where product_type='Study') li on studies.id=li.product_id
            join company_orders_production.versions v  ON v.item_id = s.id
  WHERE v.object_changes LIKE '%accepted%'
  AND v.object_changes LIKE '%requested%'
            order by s.created_at asc
            LIMIT 300000

view: session_starts {
  derived_table: {
    sql: SELECT
  s.id,
             s.account_id,
             s.created_at,
             v.created_at,
             datediff(min, s.created_at,v.created_at) as "time to start"
            FROM (select id,created_at, account_id, creator_id, study_id from company_orders_production.sessions where state='completed' and created_at>='2015-01-01') s
            JOIN (select id,email from company_orders_production.users where roles&2=0 and email not like '[email protected]' and email!='<% email_placeholder %>') u ON u.id = s.creator_id
            JOIN company_orders_production.studies ON studies.id = s.study_id
            JOIN company_orders_production.accounts ON accounts.id = s.account_id
            join (select product_id, sku_id from company_orders_production.line_items where product_type='Study') li on studies.id=li.product_id
            join company_orders_production.versions v  ON v.item_id = s.id
  WHERE v.object_changes LIKE '%accepted%'
  AND v.object_changes LIKE '%requested%'
            order by s.created_at asc
            LIMIT 300000 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: account_id {
    type: number
    sql: ${TABLE}.account_id ;;
  }

  dimension: time_to_start {
    type: number
    sql: ${TABLE}."time to start" ;;
  }

  dimension: lessthan1hr {
  }

  dimension_group: created_at {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: totallessthan1hr {
    type: sum
    sql: ${lessthan1hr} ;;
  }

  measure: percentlessthan1hr {
    type: number
    sql: 100.0 * ${totallessthan1hr} / NULLIF(${count}, 0) ;;
  }
}

- view: session_starts
  derived_table:
    sql: SELECT
  s.id,
             s.account_id,
             s.created_at,
             v.created_at,
             datediff(min, s.created_at,v.created_at) as "time to start"
            FROM (select id,created_at, account_id, creator_id, study_id from company_orders_production.sessions where state='completed' and created_at>='2015-01-01') s
            JOIN (select id,email from company_orders_production.users where roles&2=0 and email not like '[email protected]' and email!='<% email_placeholder %>') u ON u.id = s.creator_id
            JOIN company_orders_production.studies ON studies.id = s.study_id
            JOIN company_orders_production.accounts ON accounts.id = s.account_id
            join (select product_id, sku_id from company_orders_production.line_items where product_type='Study') li on studies.id=li.product_id
            join company_orders_production.versions v  ON v.item_id = s.id
  WHERE v.object_changes LIKE '%accepted%'
  AND v.object_changes LIKE '%requested%'
            order by s.created_at asc
            LIMIT 300000
  fields:
  - dimension: id
    type: number
    sql: ${TABLE}.id
  - dimension: account_id
    type: number
    sql: ${TABLE}.account_id
  - dimension: time_to_start
    type: number
    sql: ${TABLE}."time to start"
  - dimension: lessthan1hr
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: totallessthan1hr
    type: sum
    sql: ${lessthan1hr}
  - measure: percentlessthan1hr
    type: number
    sql: 100.0 * ${totallessthan1hr} / NULLIF(${count}, 0)