data.ml
dbt Streaming cinema

Rolling Premium Users

Rolling Premium Users model for streaming cinema.

inferred

Blocks

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

SELECT
  daily_use_premium.user_id
  ,daily_use_premium.user_type
  ,daily_use_premium.platform
  ,daily_use_premium.country
  ,daily_use_premium.plan_name
  ,daily_use_premium.merchant_type
  ,daily_use_premium.view_date
  ,daily_use_premium.seconds_watched
  ,dates.date as observation_date
FROM
{{ ref('daily_use_premium') }} AS daily_use_premium
LEFT JOIN  {{ ref('dates') }} AS dates
  ON dates.date  >= daily_use_premium.view_date
  AND dates.date < daily_use_premium.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use_premium.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %}

# models/schema.yml
version: 2

models:
  - name: rolling_premium_users
    columns:
      - name: country
      - name: merchant_type
      - name: minutes_watched
      - name: plan_name
      - name: platform
      - name: seconds_watched
      - name: user_id
      - name: user_type
      - name: view
      - name: watched_within_1_days
      - name: watched_within_2_days
      - name: watched_within_3_days
      - name: watched_within_4_days
      - name: watched_within_7_days
      - name: watched_within_10_days
      - name: watched_within_14_days
      - name: watched_within_21_days
      - name: watched_within_30_days

Assumptions

  • Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
  • 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.
rolling_premium_users.sql · 19 lines
source-preserved validation: not checked 
SELECT
  daily_use_premium.user_id
  ,daily_use_premium.user_type
  ,daily_use_premium.platform
  ,daily_use_premium.country
  ,daily_use_premium.plan_name
  ,daily_use_premium.merchant_type
  ,daily_use_premium.view_date
  ,daily_use_premium.seconds_watched
  ,dates.date as observation_date
FROM
${daily_use_premium.SQL_TABLE_NAME} AS daily_use_premium
LEFT JOIN  ${dates.SQL_TABLE_NAME} AS dates
  ON dates.date  >= daily_use_premium.view_date
  AND dates.date < daily_use_premium.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use_premium.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %}
rolling_premium_users.view.lkml · 197 lines
inferred 
view: rolling_premium_users {
  derived_table: {
    sql: SELECT
  daily_use_premium.user_id
  ,daily_use_premium.user_type
  ,daily_use_premium.platform
  ,daily_use_premium.country
  ,daily_use_premium.plan_name
  ,daily_use_premium.merchant_type
  ,daily_use_premium.view_date
  ,daily_use_premium.seconds_watched
  ,dates.date as observation_date
FROM
${daily_use_premium.SQL_TABLE_NAME} AS daily_use_premium
LEFT JOIN  ${dates.SQL_TABLE_NAME} AS dates
  ON dates.date  >= daily_use_premium.view_date
  AND dates.date < daily_use_premium.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use_premium.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %} ;;
  }

  dimension: country {
    type: string
    sql: ${TABLE}.country ;;
  }

  dimension: merchant_type {
    type: string
    sql: ${TABLE}.merchant_type ;;
  }

  dimension: minutes_watched {
    type: number
    hidden: yes
    sql: ${TABLE}.seconds_watched/60.0 ;;
  }

  dimension: plan_name {
    type: string
    sql: ${TABLE}.plan_name ;;
  }

  dimension: platform {
    type: string
    sql: ${TABLE}.platform ;;
  }

  dimension: seconds_watched {
    type: int
    sql: ${TABLE}.seconds_watched ;;
  }

  dimension: user_id {
    type: int
    sql: ${TABLE}.user_id ;;
  }

  dimension: user_type {
    type: string
    sql: ${TABLE}.user_type ;;
  }

  dimension: view {
    type: time
    sql: ${TABLE}.view_date ;;
  }

  dimension: watched_within_1_days {
    type: yesno
    sql: ${view_raw} + interval '1 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_2_days {
    type: yesno
    sql: ${view_raw} + interval '2 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_3_days {
    type: yesno
    sql: ${view_raw} + interval '3 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_4_days {
    type: yesno
    sql: ${view_raw} + interval '4 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_7_days {
    type: yesno
    sql: ${view_raw} + interval '7 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_10_days {
    type: yesno
    sql: ${view_raw} + interval '10 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_14_days {
    type: yesno
    sql: ${view_raw} + interval '14 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_21_days {
    type: yesno
    sql: ${view_raw} + interval '21 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

  dimension: watched_within_30_days {
    type: yesno
    sql: ${view_raw} + interval '30 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw} ;;
  }

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

  measure: total_minutes_watched {
    type: sum
    sql: ${minutes_watched} ;;
  }

  measure: total_minutes_watched_1_day {
    type: sum
    filters: [watched_within_1_days: "Yes"]
    sql: ${minutes_watched} ;;
  }

  measure: total_minutes_watched_7_days {
    type: sum
    filters: [watched_within_7_days: "Yes"]
    sql: ${minutes_watched} ;;
  }

  measure: total_minutes_watched_30_days {
    type: sum
    filters: [watched_within_30_days: "Yes"]
    sql: ${minutes_watched} ;;
  }

  measure: rolling_premium_users_1d {
    type: count_distinct
    filters: [watched_within_1_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_2d {
    type: count_distinct
    filters: [watched_within_2_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_3d {
    type: count_distinct
    filters: [watched_within_3_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_4d {
    type: count_distinct
    filters: [watched_within_4_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_7d {
    type: count_distinct
    filters: [watched_within_7_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_10d {
    type: count_distinct
    filters: [watched_within_10_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_14d {
    type: count_distinct
    filters: [watched_within_14_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_21d {
    type: count_distinct
    filters: [watched_within_21_days: "Yes"]
    sql: ${user_id} ;;
  }

  measure: rolling_premium_users_30d {
    type: count_distinct
    filters: [watched_within_30_days: "Yes"]
    sql: ${user_id} ;;
  }
}
rolling_premium_users.view.lookml · 149 lines
source-preserved 
- view: rolling_premium_users
  derived_table:
    sql: SELECT
  daily_use_premium.user_id
  ,daily_use_premium.user_type
  ,daily_use_premium.platform
  ,daily_use_premium.country
  ,daily_use_premium.plan_name
  ,daily_use_premium.merchant_type
  ,daily_use_premium.view_date
  ,daily_use_premium.seconds_watched
  ,dates.date as observation_date
FROM
${daily_use_premium.SQL_TABLE_NAME} AS daily_use_premium
LEFT JOIN  ${dates.SQL_TABLE_NAME} AS dates
  ON dates.date  >= daily_use_premium.view_date
  AND dates.date < daily_use_premium.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use_premium.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %}
  fields:
  - dimension: country
    type: string
    sql: ${TABLE}.country
  - dimension: merchant_type
    type: string
    sql: ${TABLE}.merchant_type
  - dimension: minutes_watched
    type: number
    hidden: true
    sql: ${TABLE}.seconds_watched/60.0
  - dimension: plan_name
    type: string
    sql: ${TABLE}.plan_name
  - dimension: platform
    type: string
    sql: ${TABLE}.platform
  - dimension: seconds_watched
    type: int
    sql: ${TABLE}.seconds_watched
  - dimension: user_id
    type: int
    sql: ${TABLE}.user_id
  - dimension: user_type
    type: string
    sql: ${TABLE}.user_type
  - dimension: view
    type: time
    sql: ${TABLE}.view_date
  - dimension: watched_within_1_days
    type: yesno
    sql: ${view_raw} + interval '1 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_2_days
    type: yesno
    sql: ${view_raw} + interval '2 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_3_days
    type: yesno
    sql: ${view_raw} + interval '3 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_4_days
    type: yesno
    sql: ${view_raw} + interval '4 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_7_days
    type: yesno
    sql: ${view_raw} + interval '7 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_10_days
    type: yesno
    sql: ${view_raw} + interval '10 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_14_days
    type: yesno
    sql: ${view_raw} + interval '14 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_21_days
    type: yesno
    sql: ${view_raw} + interval '21 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension: watched_within_30_days
    type: yesno
    sql: ${view_raw} + interval '30 day' > ${observation_raw} AND  ${view_raw} <= ${observation_raw}
  - dimension_group: observation
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - raw
    sql: ${TABLE}.observation_date
  - measure: total_minutes_watched
    type: sum
    sql: ${minutes_watched}
  - measure: total_minutes_watched_1_day
    type: sum
    filters:
    - watched_within_1_days: Yes
    sql: ${minutes_watched}
  - measure: total_minutes_watched_7_days
    type: sum
    filters:
    - watched_within_7_days: Yes
    sql: ${minutes_watched}
  - measure: total_minutes_watched_30_days
    type: sum
    filters:
    - watched_within_30_days: Yes
    sql: ${minutes_watched}
  - measure: rolling_premium_users_1d
    type: count_distinct
    filters:
    - watched_within_1_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_2d
    type: count_distinct
    filters:
    - watched_within_2_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_3d
    type: count_distinct
    filters:
    - watched_within_3_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_4d
    type: count_distinct
    filters:
    - watched_within_4_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_7d
    type: count_distinct
    filters:
    - watched_within_7_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_10d
    type: count_distinct
    filters:
    - watched_within_10_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_14d
    type: count_distinct
    filters:
    - watched_within_14_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_21d
    type: count_distinct
    filters:
    - watched_within_21_days: Yes
    sql: ${user_id}
  - measure: rolling_premium_users_30d
    type: count_distinct
    filters:
    - watched_within_30_days: Yes
    sql: ${user_id}

Contents

  • rolling_premium_users.sql
  • rolling_premium_users.sql
  • rolling_premium_users.view.lkml
  • rolling_premium_users.view.lookml