Rolling Active Users

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

SELECT
  daily_use.user_id
  ,daily_use.is_premium
  ,daily_use.view_date
  ,daily_use.number_of_watches
  ,dates.date as observation_date
FROM
{{ ref('daily_use') }} AS daily_use
LEFT JOIN  {{ ref('dates') }} AS dates
ON
dates.date  >= daily_use.view_date
AND  dates.date < daily_use.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %}

# models/schema.yml
version: 2

models:
  - name: rolling_active_users
    columns:
      - name: is_premium
      - name: number_of_watches
      - name: user_id
      - name: view
        description: "Actual date that the user viewed content"
      - name: watched_within_7_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.

SELECT
  daily_use.user_id
  ,daily_use.is_premium
  ,daily_use.view_date
  ,daily_use.number_of_watches
  ,dates.date as observation_date
FROM
${daily_use.SQL_TABLE_NAME} AS daily_use
LEFT JOIN  ${dates.SQL_TABLE_NAME} AS dates
ON
dates.date  >= daily_use.view_date
AND  dates.date < daily_use.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %}

view: rolling_active_users {
  derived_table: {
    sql: SELECT
  daily_use.user_id
  ,daily_use.is_premium
  ,daily_use.view_date
  ,daily_use.number_of_watches
  ,dates.date as observation_date
FROM
${daily_use.SQL_TABLE_NAME} AS daily_use
LEFT JOIN  ${dates.SQL_TABLE_NAME} AS dates
ON
dates.date  >= daily_use.view_date
AND  dates.date < daily_use.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %} ;;
  }

  dimension: is_premium {
    type: yesno
    sql: ${TABLE}.is_premium = 1 ;;
  }

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

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

  dimension: view {
    description: "Actual date that the user viewed content"
    type: time
    sql: ${TABLE}.view_date ;;
  }

  dimension: watched_within_7_days {
    type: yesno
    sql: ${view_raw} + interval '7 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 {
    description: "As of the observation date, how many users were active within X days prior?"
    type: time
    timeframes: [time, date, week, month, raw]
    sql: ${TABLE}.observation_date ;;
  }

  measure: total_number_of_watches {
    type: sum
    sql: ${number_of_watches} ;;
  }

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

  measure: rolling_active_users_30d {
    type: count_distinct
    filters: [watched_within_30_days: "Yes"]
    sql: ${user_id} ;;
  }
}

- view: rolling_active_users
  derived_table:
    sql: SELECT
  daily_use.user_id
  ,daily_use.is_premium
  ,daily_use.view_date
  ,daily_use.number_of_watches
  ,dates.date as observation_date
FROM
${daily_use.SQL_TABLE_NAME} AS daily_use
LEFT JOIN  ${dates.SQL_TABLE_NAME} AS dates
ON
dates.date  >= daily_use.view_date
AND  dates.date < daily_use.view_date + interval '30 day'
WHERE
 {% condition view_date %} daily_use.view_date {% endcondition %}
 AND
 {% condition observation_date %} dates.date {% endcondition %}
  fields:
  - dimension: is_premium
    type: yesno
    sql: ${TABLE}.is_premium = 1
  - dimension: number_of_watches
    type: int
    sql: ${TABLE}.number_of_watches
  - dimension: user_id
    type: int
    sql: ${TABLE}.user_id
  - dimension: view
    type: time
    description: Actual date that the user viewed content
    sql: ${TABLE}.view_date
  - dimension: watched_within_7_days
    type: yesno
    sql: ${view_raw} + interval '7 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_number_of_watches
    type: sum
    sql: ${number_of_watches}
  - measure: rolling_active_users_7d
    type: count_distinct
    filters:
    - watched_within_7_days: Yes
    sql: ${user_id}
  - measure: rolling_active_users_30d
    type: count_distinct
    filters:
    - watched_within_30_days: Yes
    sql: ${user_id}