SQL Entertainment tech

Rooms Activity Facts

Rooms Activity Facts model for entertainment tech.

source-preserved

Blocks

dbt SQL LKML LookML

rooms_activity_facts.sql · 80 lines

inferred

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

SELECT
  start_event.statistic_value AS start_room
  , start_event.play_session AS session_id
  , ended_event.statistic_value AS end_room
  , start_event.sequence AS start_sequence
  , ended_event.sequence AS ended_sequence
  , start_event.time_utc AS room_start
  , ended_event.time_utc AS room_end
  , (SELECT COUNT(DISTINCT ss3.statistic_value)
    FROM statistic_string AS ss3
    WHERE ss3.category = 'CHAR_SPOKE'
      AND ss3.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss3.play_session = start_event.play_session
      AND ss3.sequence > start_event.sequence
      AND ss3.sequence < ended_event.sequence) AS char_spoke_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss4
    WHERE ss4.category = 'MIC_BUTTON_HELD'
      AND ss4.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss4.play_session = start_event.play_session
      AND ss4.sequence > start_event.sequence
      AND ss4.sequence < ended_event.sequence) AS mic_pressed_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss5
    WHERE ss5.category = 'USER_SPOKE'
      AND ss5.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss5.play_session = start_event.play_session
      AND ss5.sequence > start_event.sequence
      AND ss5.sequence < ended_event.sequence) AS user_spoke_count
FROM
  (SELECT
    l1.start_id AS start_id
    , l1.sequence AS sequence
    , l1.session_id AS session_id
    , (SELECT ss2.id
      FROM statistic_string AS ss2
      WHERE ss2.category = 'ACTIVITY_CONVERSATION_ENDED'
        AND ss2.play_session = l1.session_id
        AND ss2.sequence > l1.sequence
        AND ss2.application IN ('ShowName', 'TheShowName')
        AND ss2.statistic_type='ACTIVITY_NAME'
        AND ss2.statistic_value = l1.start_room
      ORDER BY sequence ASC
      LIMIT 1) AS ended_id
  FROM
    (SELECT
      ss1.id AS start_id
      , ss1.sequence AS sequence
      , ss1.play_session AS session_id
      , ss1.statistic_value AS start_room
    FROM statistic_string AS ss1
    WHERE ss1.category = 'ACTIVITY_CONVERSATION_BEGAN'
      AND ss1.application IN ('ShowName', 'TheShowName')
      AND ss1.statistic_type='ACTIVITY_NAME'
      AND ss1.time_utc > '2013-06-01'
    ORDER BY time_utc ASC
    ) AS l1
  ) AS l2
LEFT JOIN statistic_string AS start_event
ON start_event.id = l2.start_id
LEFT JOIN statistic_string AS ended_event
ON ended_event.id =  l2.ended_id

# models/schema.yml
version: 2

models:
  - name: rooms_activity_facts
    columns:
      - name: start_room
      - name: end_room
      - name: start_sequence
      - name: ended_sequence
      - name: session_id
      - name: char_spoke_count
      - name: mic_pressed_count
      - name: user_spoke_count

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.

rooms_activity_facts.sql · 62 lines

source-preserved validation: parses

SELECT
  start_event.statistic_value AS start_room
  , start_event.play_session AS session_id
  , ended_event.statistic_value AS end_room
  , start_event.sequence AS start_sequence
  , ended_event.sequence AS ended_sequence
  , start_event.time_utc AS room_start
  , ended_event.time_utc AS room_end
  , (SELECT COUNT(DISTINCT ss3.statistic_value)
    FROM statistic_string AS ss3
    WHERE ss3.category = 'CHAR_SPOKE'
      AND ss3.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss3.play_session = start_event.play_session
      AND ss3.sequence > start_event.sequence
      AND ss3.sequence < ended_event.sequence) AS char_spoke_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss4
    WHERE ss4.category = 'MIC_BUTTON_HELD'
      AND ss4.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss4.play_session = start_event.play_session
      AND ss4.sequence > start_event.sequence
      AND ss4.sequence < ended_event.sequence) AS mic_pressed_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss5
    WHERE ss5.category = 'USER_SPOKE'
      AND ss5.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss5.play_session = start_event.play_session
      AND ss5.sequence > start_event.sequence
      AND ss5.sequence < ended_event.sequence) AS user_spoke_count
FROM
  (SELECT
    l1.start_id AS start_id
    , l1.sequence AS sequence
    , l1.session_id AS session_id
    , (SELECT ss2.id
      FROM statistic_string AS ss2
      WHERE ss2.category = 'ACTIVITY_CONVERSATION_ENDED'
        AND ss2.play_session = l1.session_id
        AND ss2.sequence > l1.sequence
        AND ss2.application IN ('ShowName', 'TheShowName')
        AND ss2.statistic_type='ACTIVITY_NAME'
        AND ss2.statistic_value = l1.start_room
      ORDER BY sequence ASC
      LIMIT 1) AS ended_id
  FROM
    (SELECT
      ss1.id AS start_id
      , ss1.sequence AS sequence
      , ss1.play_session AS session_id
      , ss1.statistic_value AS start_room
    FROM statistic_string AS ss1
    WHERE ss1.category = 'ACTIVITY_CONVERSATION_BEGAN'
      AND ss1.application IN ('ShowName', 'TheShowName')
      AND ss1.statistic_type='ACTIVITY_NAME'
      AND ss1.time_utc > '2013-06-01'
    ORDER BY time_utc ASC
    ) AS l1
  ) AS l2
LEFT JOIN statistic_string AS start_event
ON start_event.id = l2.start_id
LEFT JOIN statistic_string AS ended_event
ON ended_event.id =  l2.ended_id

rooms_activity_facts.view.lkml · 152 lines

inferred

view: rooms_activity_facts {
  derived_table: {
    sql: SELECT
  start_event.statistic_value AS start_room
  , start_event.play_session AS session_id
  , ended_event.statistic_value AS end_room
  , start_event.sequence AS start_sequence
  , ended_event.sequence AS ended_sequence
  , start_event.time_utc AS room_start
  , ended_event.time_utc AS room_end
  , (SELECT COUNT(DISTINCT ss3.statistic_value)
    FROM statistic_string AS ss3
    WHERE ss3.category = 'CHAR_SPOKE'
      AND ss3.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss3.play_session = start_event.play_session
      AND ss3.sequence > start_event.sequence
      AND ss3.sequence < ended_event.sequence) AS char_spoke_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss4
    WHERE ss4.category = 'MIC_BUTTON_HELD'
      AND ss4.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss4.play_session = start_event.play_session
      AND ss4.sequence > start_event.sequence
      AND ss4.sequence < ended_event.sequence) AS mic_pressed_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss5
    WHERE ss5.category = 'USER_SPOKE'
      AND ss5.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss5.play_session = start_event.play_session
      AND ss5.sequence > start_event.sequence
      AND ss5.sequence < ended_event.sequence) AS user_spoke_count
FROM
  (SELECT
    l1.start_id AS start_id
    , l1.sequence AS sequence
    , l1.session_id AS session_id
    , (SELECT ss2.id
      FROM statistic_string AS ss2
      WHERE ss2.category = 'ACTIVITY_CONVERSATION_ENDED'
        AND ss2.play_session = l1.session_id
        AND ss2.sequence > l1.sequence
        AND ss2.application IN ('ShowName', 'TheShowName')
        AND ss2.statistic_type='ACTIVITY_NAME'
        AND ss2.statistic_value = l1.start_room
      ORDER BY sequence ASC
      LIMIT 1) AS ended_id
  FROM
    (SELECT
      ss1.id AS start_id
      , ss1.sequence AS sequence
      , ss1.play_session AS session_id
      , ss1.statistic_value AS start_room
    FROM statistic_string AS ss1
    WHERE ss1.category = 'ACTIVITY_CONVERSATION_BEGAN'
      AND ss1.application IN ('ShowName', 'TheShowName')
      AND ss1.statistic_type='ACTIVITY_NAME'
      AND ss1.time_utc > '2013-06-01'
    ORDER BY time_utc ASC
    ) AS l1
  ) AS l2
LEFT JOIN statistic_string AS start_event
ON start_event.id = l2.start_id
LEFT JOIN statistic_string AS ended_event
ON ended_event.id =  l2.ended_id ;;
  }

  dimension: start_room {
  }

  dimension: end_room {
  }

  dimension: start_sequence {
  }

  dimension: ended_sequence {
  }

  dimension: session_id {
  }

  dimension: char_spoke_count {
    type: int
  }

  dimension: mic_pressed_count {
    type: int
  }

  dimension: user_spoke_count {
    type: int
  }

  dimension_group: room_start {
    type: time
    timeframes: [time, date, week, month]
  }

  dimension_group: room_end {
    type: time
    timeframes: [time, date, week, month]
  }

  measure: total_char_spoke {
    type: sum
    sql: ${char_spoke_count} ;;
  }

  measure: total_char_spoke_per_user {
    type: number
    sql: ${total_char_spoke} / ${account.count} ;;
  }

  measure: total_mic_pressed {
    type: sum
    sql: ${mic_pressed_count} ;;
  }

  measure: total_mic_pressed_per_user {
    type: number
    sql: ${total_mic_pressed} / ${account.count} ;;
  }

  measure: total_user_spoke {
    type: sum
    sql: ${user_spoke_count} ;;
  }

  measure: total_user_spoke_per_user {
    type: number
    sql: ${total_user_spoke} / ${account.count} ;;
  }

  measure: total_activities {
    type: number
    sql: ${total_char_spoke} + ${total_mic_pressed} + ${total_user_spoke} ;;
  }

  measure: activities_per_user {
    type: number
    sql: 1.0 * ${total_activities} / ${account.count} ;;
  }

  measure: activities_per_room_per_user {
    type: number
    sql: 1.0 * ${total_activities} / ${room_count} / ${account.count} ;;
  }

  measure: room_count {
    type: count
  }
}

rooms_activity_facts.view.lookml · 119 lines

source-preserved

- view: rooms_activity_facts
  derived_table:
    sql: SELECT
  start_event.statistic_value AS start_room
  , start_event.play_session AS session_id
  , ended_event.statistic_value AS end_room
  , start_event.sequence AS start_sequence
  , ended_event.sequence AS ended_sequence
  , start_event.time_utc AS room_start
  , ended_event.time_utc AS room_end
  , (SELECT COUNT(DISTINCT ss3.statistic_value)
    FROM statistic_string AS ss3
    WHERE ss3.category = 'CHAR_SPOKE'
      AND ss3.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss3.play_session = start_event.play_session
      AND ss3.sequence > start_event.sequence
      AND ss3.sequence < ended_event.sequence) AS char_spoke_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss4
    WHERE ss4.category = 'MIC_BUTTON_HELD'
      AND ss4.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss4.play_session = start_event.play_session
      AND ss4.sequence > start_event.sequence
      AND ss4.sequence < ended_event.sequence) AS mic_pressed_count
  , (SELECT COUNT(*)
    FROM statistic_string AS ss5
    WHERE ss5.category = 'USER_SPOKE'
      AND ss5.statistic_type = 'CURRENT_ACTIVITY_NAME'
      AND ss5.play_session = start_event.play_session
      AND ss5.sequence > start_event.sequence
      AND ss5.sequence < ended_event.sequence) AS user_spoke_count
FROM
  (SELECT
    l1.start_id AS start_id
    , l1.sequence AS sequence
    , l1.session_id AS session_id
    , (SELECT ss2.id
      FROM statistic_string AS ss2
      WHERE ss2.category = 'ACTIVITY_CONVERSATION_ENDED'
        AND ss2.play_session = l1.session_id
        AND ss2.sequence > l1.sequence
        AND ss2.application IN ('ShowName', 'TheShowName')
        AND ss2.statistic_type='ACTIVITY_NAME'
        AND ss2.statistic_value = l1.start_room
      ORDER BY sequence ASC
      LIMIT 1) AS ended_id
  FROM
    (SELECT
      ss1.id AS start_id
      , ss1.sequence AS sequence
      , ss1.play_session AS session_id
      , ss1.statistic_value AS start_room
    FROM statistic_string AS ss1
    WHERE ss1.category = 'ACTIVITY_CONVERSATION_BEGAN'
      AND ss1.application IN ('ShowName', 'TheShowName')
      AND ss1.statistic_type='ACTIVITY_NAME'
      AND ss1.time_utc > '2013-06-01'
    ORDER BY time_utc ASC
    ) AS l1
  ) AS l2
LEFT JOIN statistic_string AS start_event
ON start_event.id = l2.start_id
LEFT JOIN statistic_string AS ended_event
ON ended_event.id =  l2.ended_id
  fields:
  - dimension: start_room
  - dimension: end_room
  - dimension: start_sequence
  - dimension: ended_sequence
  - dimension: session_id
  - dimension: char_spoke_count
    type: int
  - dimension: mic_pressed_count
    type: int
  - dimension: user_spoke_count
    type: int
  - dimension_group: room_start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
  - dimension_group: room_end
    type: time
    timeframes:
    - time
    - date
    - week
    - month
  - measure: total_char_spoke
    type: sum
    sql: ${char_spoke_count}
  - measure: total_char_spoke_per_user
    type: number
    sql: ${total_char_spoke} / ${account.count}
  - measure: total_mic_pressed
    type: sum
    sql: ${mic_pressed_count}
  - measure: total_mic_pressed_per_user
    type: number
    sql: ${total_mic_pressed} / ${account.count}
  - measure: total_user_spoke
    type: sum
    sql: ${user_spoke_count}
  - measure: total_user_spoke_per_user
    type: number
    sql: ${total_user_spoke} / ${account.count}
  - measure: total_activities
    type: number
    sql: ${total_char_spoke} + ${total_mic_pressed} + ${total_user_spoke}
  - measure: activities_per_user
    type: number
    sql: 1.0 * ${total_activities} / ${account.count}
  - measure: activities_per_room_per_user
    type: number
    sql: 1.0 * ${total_activities} / ${room_count} / ${account.count}
  - measure: room_count
    type: count

rooms_activity_facts.sql
rooms_activity_facts.sql
rooms_activity_facts.view.lkml
rooms_activity_facts.view.lookml

Rooms Activity Facts

Blocks

Assumptions

Contents