Active Users

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

WITH daily_use AS (
  SELECT uid as user_id
        ,action
        ,DATE_TRUNC('day', start_date) as activity_date
  FROM ts_user_base
  )
  SELECT daily_use.user_id
        ,daily_use.action
        ,wd.date as window_date
        ,daily_use.activity_date
  FROM daily_use
      ,{{ ref('dates') }} AS wd
  WHERE wd.date >= daily_use.activity_date
    AND wd.date < daily_use.activity_date + interval '30 day'
    AND {% condition activity_date %} daily_use.activity_date {% endcondition %}
    AND {% condition window_date %} wd.date {% endcondition %}

# models/schema.yml
version: 2

models:
  - name: active_users
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: action
      - name: active_day
      - name: active_7_day

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.

WITH daily_use AS (
  SELECT uid as user_id
        ,action
        ,DATE_TRUNC('day', start_date) as activity_date
  FROM ts_user_base
  )
  SELECT daily_use.user_id
        ,daily_use.action
        ,wd.date as window_date
        ,daily_use.activity_date
  FROM daily_use
      ,${dates.SQL_TABLE_NAME} AS wd
  WHERE wd.date >= daily_use.activity_date
    AND wd.date < daily_use.activity_date + interval '30 day'
    AND {% condition activity_date %} daily_use.activity_date {% endcondition %}
    AND {% condition window_date %} wd.date {% endcondition %}

view: active_users {
  derived_table: {
    sql: WITH daily_use AS (
  SELECT uid as user_id
        ,action
        ,DATE_TRUNC('day', start_date) as activity_date
  FROM ts_user_base
  )
  SELECT daily_use.user_id
        ,daily_use.action
        ,wd.date as window_date
        ,daily_use.activity_date
  FROM daily_use
      ,${dates.SQL_TABLE_NAME} AS wd
  WHERE wd.date >= daily_use.activity_date
    AND wd.date < daily_use.activity_date + interval '30 day'
    AND {% condition activity_date %} daily_use.activity_date {% endcondition %}
    AND {% condition window_date %} wd.date {% endcondition %} ;;
  }

  dimension: user_id {
    primary_key: yes
    sql: ${TABLE}.user_id ;;
  }

  dimension: action {
    sql: ${TABLE}.action ;;
  }

  dimension: active_day {
    type: yesno
    sql: ${TABLE}.activity_date = ${TABLE}.window_date ;;
  }

  dimension: active_7_day {
    type: yesno
    sql: (${TABLE}.window_date::date - ${TABLE}.activity_date::date) < 7 ;;
  }

  dimension_group: activity {
    type: time
    timeframes: [date, week, month, year]
    convert_tz: no
    sql: ${TABLE}.activity_date ;;
  }

  dimension_group: window {
    type: time
    timeframes: [date, week, month, year]
    convert_tz: no
    sql: ${TABLE}.window_date ;;
  }

  measure: user_count_active_this_day {
    type: count_distinct
    filters: [active_day: "True"]
    sql: ${user_id} ;;
  }

  measure: user_count_active_7_days {
    type: count_distinct
    filters: [active_7_day: "True"]
    sql: ${user_id} ;;
  }

  measure: user_count_active_30_days {
    type: count_distinct
    sql: ${user_id} ;;
  }
}

- view: active_users
  derived_table:
    sql: WITH daily_use AS (
  SELECT uid as user_id
        ,action
        ,DATE_TRUNC('day', start_date) as activity_date
  FROM ts_user_base
  )
  SELECT daily_use.user_id
        ,daily_use.action
        ,wd.date as window_date
        ,daily_use.activity_date
  FROM daily_use
      ,${dates.SQL_TABLE_NAME} AS wd
  WHERE wd.date >= daily_use.activity_date
    AND wd.date < daily_use.activity_date + interval '30 day'
    AND {% condition activity_date %} daily_use.activity_date {% endcondition %}
    AND {% condition window_date %} wd.date {% endcondition %}
  fields:
  - dimension: user_id
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: action
    sql: ${TABLE}.action
  - dimension: active_day
    type: yesno
    sql: ${TABLE}.activity_date = ${TABLE}.window_date
  - dimension: active_7_day
    type: yesno
    sql: (${TABLE}.window_date::date - ${TABLE}.activity_date::date) < 7
  - dimension_group: activity
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    convert_tz: false
    sql: ${TABLE}.activity_date
  - dimension_group: window
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    convert_tz: false
    sql: ${TABLE}.window_date
  - measure: user_count_active_this_day
    type: count_distinct
    filters:
    - active_day: True
    sql: ${user_id}
  - measure: user_count_active_7_days
    type: count_distinct
    filters:
    - active_7_day: True
    sql: ${user_id}
  - measure: user_count_active_30_days
    type: count_distinct
    sql: ${user_id}