Sessions

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

SELECT
  ss.play_session AS session_id
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , ss.account AS account
  , ss.install AS install
  , SUM(CASE WHEN ss.category = 'MIC_BUTTON_HELD' THEN 1 ELSE 0 END) AS mic_presses
  , SUM(CASE WHEN ss.statistic_type = "ACTIVITY_NAME" THEN 1 ELSE 0 END) AS session_activities
  , SUM(CASE WHEN ss.category = 'ACTIVITY_CONVERSATION_BEGAN' THEN 1 ELSE 0 END) AS session_rooms
  , MIN(ss.time_utc) AS first_event
  , MAX(ss.time_utc) AS last_event
  , COUNT(*) AS session_events
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
GROUP BY session_id

# models/schema.yml
version: 2

models:
  - name: sessions
    columns:
      - name: session_id
        tests:
          - unique
          - not_null
      - name: first_os_version
      - name: session_rooms
      - name: session_activities
      - name: session_events
      - name: install
      - name: account
      - name: mic_presses
      - name: pressed_mic
      - name: session_before_consent
      - name: duration_min
      - name: duration_min_tier
      - name: duration_hour
      - name: history

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
  ss.play_session AS session_id
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , ss.account AS account
  , ss.install AS install
  , SUM(CASE WHEN ss.category = 'MIC_BUTTON_HELD' THEN 1 ELSE 0 END) AS mic_presses
  , SUM(CASE WHEN ss.statistic_type = "ACTIVITY_NAME" THEN 1 ELSE 0 END) AS session_activities
  , SUM(CASE WHEN ss.category = 'ACTIVITY_CONVERSATION_BEGAN' THEN 1 ELSE 0 END) AS session_rooms
  , MIN(ss.time_utc) AS first_event
  , MAX(ss.time_utc) AS last_event
  , COUNT(*) AS session_events
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
GROUP BY session_id

view: sessions {
  derived_table: {
    sql: SELECT
  ss.play_session AS session_id
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , ss.account AS account
  , ss.install AS install
  , SUM(CASE WHEN ss.category = 'MIC_BUTTON_HELD' THEN 1 ELSE 0 END) AS mic_presses
  , SUM(CASE WHEN ss.statistic_type = "ACTIVITY_NAME" THEN 1 ELSE 0 END) AS session_activities
  , SUM(CASE WHEN ss.category = 'ACTIVITY_CONVERSATION_BEGAN' THEN 1 ELSE 0 END) AS session_rooms
  , MIN(ss.time_utc) AS first_event
  , MAX(ss.time_utc) AS last_event
  , COUNT(*) AS session_events
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
GROUP BY session_id ;;
  }

  dimension: session_id {
    primary_key: yes
  }

  dimension: first_os_version {
  }

  dimension: session_rooms {
    type: int
  }

  dimension: session_activities {
    type: int
  }

  dimension: session_events {
    type: int
  }

  dimension: install {
  }

  dimension: account {
  }

  dimension: mic_presses {
    type: int
  }

  dimension: pressed_mic {
    type: yesno
    sql: ${mic_presses} > 0 ;;
  }

  dimension: session_before_consent {
    type: yesno
    sql: ${start_date} < ${account_event_facts.consent_date} ;;
  }

  dimension: duration_min {
    sql: TIMESTAMPDIFF(minute, ${TABLE}.first_event, ${TABLE}.last_event) ;;
  }

  dimension: duration_min_tier {
    type: tier
    sql: ${duration_min} ;;
  }

  dimension: duration_hour {
    type: int
    sql: FLOOR(${duration_min}/60) ;;
  }

  dimension: history {
    sql: ${session_id} ;;
  }

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

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

  measure: count {
    type: count
  }

  measure: percent_of_total {
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: average_duration_min {
    type: avg
    sql: ${duration_min} ;;
  }

  measure: total_duration_min {
    type: sum
    sql: ${duration_min} ;;
  }

  measure: average_duration_hour {
    type: avg
    sql: ${duration_hour} ;;
  }

  measure: total_duration_hour {
    type: sum
    sql: ${duration_hour} ;;
  }

  measure: accounts.count {
    type: count_distinct
    sql: ${TABLE}.account ;;
  }

  measure: sessions_per_user {
    type: number
    sql: ${count}/NULLIF(${accounts.count},0) ;;
  }

  measure: active_days_usage {
    type: count_distinct
    sql: EXTRACT(YEAR_MONTH FROM ${TABLE}.first_event) ;;
  }
}

- view: sessions
  derived_table:
    sql: SELECT
  ss.play_session AS session_id
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , ss.account AS account
  , ss.install AS install
  , SUM(CASE WHEN ss.category = 'MIC_BUTTON_HELD' THEN 1 ELSE 0 END) AS mic_presses
  , SUM(CASE WHEN ss.statistic_type = "ACTIVITY_NAME" THEN 1 ELSE 0 END) AS session_activities
  , SUM(CASE WHEN ss.category = 'ACTIVITY_CONVERSATION_BEGAN' THEN 1 ELSE 0 END) AS session_rooms
  , MIN(ss.time_utc) AS first_event
  , MAX(ss.time_utc) AS last_event
  , COUNT(*) AS session_events
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
GROUP BY session_id
  fields:
  - dimension: session_id
    primary_key: true
  - dimension: first_os_version
  - dimension: session_rooms
    type: int
  - dimension: session_activities
    type: int
  - dimension: session_events
    type: int
  - dimension: install
  - dimension: account
  - dimension: mic_presses
    type: int
  - dimension: pressed_mic
    type: yesno
    sql: ${mic_presses} > 0
  - dimension: session_before_consent
    type: yesno
    sql: ${start_date} < ${account_event_facts.consent_date}
  - dimension: duration_min
    sql: TIMESTAMPDIFF(minute, ${TABLE}.first_event, ${TABLE}.last_event)
  - dimension: duration_min_tier
    type: tier
    sql: ${duration_min}
  - dimension: duration_hour
    type: int
    sql: FLOOR(${duration_min}/60)
  - dimension: history
    sql: ${session_id}
  - dimension_group: start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_event
  - dimension_group: end
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.last_event
  - measure: count
    type: count
  - measure: percent_of_total
    type: percent_of_total
    sql: ${count}
  - measure: average_duration_min
    type: avg
    sql: ${duration_min}
  - measure: total_duration_min
    type: sum
    sql: ${duration_min}
  - measure: average_duration_hour
    type: avg
    sql: ${duration_hour}
  - measure: total_duration_hour
    type: sum
    sql: ${duration_hour}
  - measure: accounts.count
    type: count_distinct
    sql: ${TABLE}.account
  - measure: sessions_per_user
    type: number
    sql: ${count}/NULLIF(${accounts.count},0)
  - measure: active_days_usage
    type: count_distinct
    sql: EXTRACT(YEAR_MONTH FROM ${TABLE}.first_event)