data.ml
dbt Entertainment tech

Account Session Facts

Account Session Facts model for entertainment tech.

inferred

Blocks

account_session_facts.sql · 40 lines
inferred 
-- models/account_session_facts.sql
{{ config(materialized='view') }}

SELECT
  s.account AS account
  , s.install AS install
  , SUM(TIMESTAMPDIFF(minute, s.first_event, s.last_event)) AS lifetime_duration_min
  , COUNT(*) AS lifetime_sessions
FROM
  (
    SELECT
      ss.play_session AS session_id
      , ss.account AS account
      , ss.install AS install
      , MIN(ss.time_utc) AS first_event
      , MAX(ss.time_utc) AS last_event
    FROM statistic_string AS ss
    WHERE ss.application  IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
    GROUP BY session_id
  ) AS s
GROUP BY 1,2

# models/schema.yml
version: 2

models:
  - name: account_session_facts
    columns:
      - name: install
        tests:
          - unique
          - not_null
      - name: account
      - name: lifetime_duration_min
      - name: lifetime_duration_min_tier
      - name: lifetime_sessions
      - name: lifetime_sessions_tier
      - name: avg_session_duration_min
      - name: avg_session_duration_min_tier

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.
account_session_facts.sql · 19 lines
source-preserved validation: parses 
SELECT
  s.account AS account
  , s.install AS install
  , SUM(TIMESTAMPDIFF(minute, s.first_event, s.last_event)) AS lifetime_duration_min
  , COUNT(*) AS lifetime_sessions
FROM
  (
    SELECT
      ss.play_session AS session_id
      , ss.account AS account
      , ss.install AS install
      , MIN(ss.time_utc) AS first_event
      , MAX(ss.time_utc) AS last_event
    FROM statistic_string AS ss
    WHERE ss.application  IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
    GROUP BY session_id
  ) AS s
GROUP BY 1,2
account_session_facts.view.lkml · 62 lines
inferred 
view: account_session_facts {
  derived_table: {
    sql: SELECT
  s.account AS account
  , s.install AS install
  , SUM(TIMESTAMPDIFF(minute, s.first_event, s.last_event)) AS lifetime_duration_min
  , COUNT(*) AS lifetime_sessions
FROM
  (
    SELECT
      ss.play_session AS session_id
      , ss.account AS account
      , ss.install AS install
      , MIN(ss.time_utc) AS first_event
      , MAX(ss.time_utc) AS last_event
    FROM statistic_string AS ss
    WHERE ss.application  IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
    GROUP BY session_id
  ) AS s
GROUP BY 1,2 ;;
  }

  dimension: install {
    primary_key: yes
  }

  dimension: account {
  }

  dimension: lifetime_duration_min {
    type: int
  }

  dimension: lifetime_duration_min_tier {
    type: tier
    sql: ${lifetime_duration_min} ;;
  }

  dimension: lifetime_sessions {
    type: int
  }

  dimension: lifetime_sessions_tier {
    type: tier
    sql: ${lifetime_sessions} ;;
  }

  dimension: avg_session_duration_min {
    type: number
    sql: CAST(${lifetime_duration_min}/NULLIF(${lifetime_sessions},0) AS DECIMAL(65,2)) ;;
  }

  dimension: avg_session_duration_min_tier {
    type: tier
    sql: ${avg_session_duration_min} ;;
  }

  measure: count {
    type: count
  }
}
account_session_facts.view.lookml · 43 lines
source-preserved 
- view: account_session_facts
  derived_table:
    sql: SELECT
  s.account AS account
  , s.install AS install
  , SUM(TIMESTAMPDIFF(minute, s.first_event, s.last_event)) AS lifetime_duration_min
  , COUNT(*) AS lifetime_sessions
FROM
  (
    SELECT
      ss.play_session AS session_id
      , ss.account AS account
      , ss.install AS install
      , MIN(ss.time_utc) AS first_event
      , MAX(ss.time_utc) AS last_event
    FROM statistic_string AS ss
    WHERE ss.application  IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-06-01'
    GROUP BY session_id
  ) AS s
GROUP BY 1,2
  fields:
  - dimension: install
    primary_key: true
  - dimension: account
  - dimension: lifetime_duration_min
    type: int
  - dimension: lifetime_duration_min_tier
    type: tier
    sql: ${lifetime_duration_min}
  - dimension: lifetime_sessions
    type: int
  - dimension: lifetime_sessions_tier
    type: tier
    sql: ${lifetime_sessions}
  - dimension: avg_session_duration_min
    type: number
    sql: CAST(${lifetime_duration_min}/NULLIF(${lifetime_sessions},0) AS DECIMAL(65,2))
  - dimension: avg_session_duration_min_tier
    type: tier
    sql: ${avg_session_duration_min}
  - measure: count
    type: count

Contents

  • account_session_facts.sql
  • account_session_facts.sql
  • account_session_facts.view.lkml
  • account_session_facts.view.lookml