Monthly User Stats

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

select date_format(dates.date, '%Y-%m') as month
      ,partner_id
      ,count(1) as num_users
      ,count(case when expiration_date > dates.date or expiration_date is null then 1 end) as num_active_users
      ,count(case when (expiration_date > last_day(dates.date - interval 1 month) or expiration_date is null)
        and date(users.created_adjusted) <= last_day(dates.date - interval 1 month) then 1 end) as last_month_active_users
from {{ ref('dates') }} as dates
inner join {{ ref('users') }} as users
  on (dayofmonth(dates.date + interval 1 day) = 1 or dates.date = current_date)
 and date(users.created_adjusted) <= dates.date
where
  users.user_id not in (
    select
      promos.user_id
    from
      {{ ref('promotions_table') }} as promos
    where
      promos.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>', '<% parameter promo_alias2 %>')
    and (
      DATEDIFF(curdate(), DATE(users.created_on)) <= 8
      OR
      DATEDIFF(users.expiration_date, DATE(users.created_on)) <= 20
    )
 )
AND users.complementary <> 1
AND
  users.user_id not in (
    select
      user_id
    from
      {{ ref('users') }} as u
    where
      datediff(u.expiration_date, date(u.created_on)) <= 20
    )
group by 1, 2
order by 1,2 desc

# models/schema.yml
version: 2

models:
  - name: monthly_user_stats
    columns:
      - name: month
      - name: partner_id
        tests:
          - unique
          - not_null
      - name: num_users_scorecard
      - name: num_active_users_scorecard

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 date_format(dates.date, '%Y-%m') as month
      ,partner_id
      ,count(1) as num_users
      ,count(case when expiration_date > dates.date or expiration_date is null then 1 end) as num_active_users
      ,count(case when (expiration_date > last_day(dates.date - interval 1 month) or expiration_date is null)
        and date(users.created_adjusted) <= last_day(dates.date - interval 1 month) then 1 end) as last_month_active_users
from ${dates.SQL_TABLE_NAME} as dates
inner join ${users.SQL_TABLE_NAME} as users
  on (dayofmonth(dates.date + interval 1 day) = 1 or dates.date = current_date)
 and date(users.created_adjusted) <= dates.date
where
  users.user_id not in (
    select
      promos.user_id
    from
      ${promotions_table.SQL_TABLE_NAME} as promos
    where
      promos.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>', '<% parameter promo_alias2 %>')
    and (
      DATEDIFF(curdate(), DATE(users.created_on)) <= 8
      OR
      DATEDIFF(users.expiration_date, DATE(users.created_on)) <= 20
    )
 )
AND users.complementary <> 1
AND
  users.user_id not in (
    select
      user_id
    from
      ${users.SQL_TABLE_NAME} as u
    where
      datediff(u.expiration_date, date(u.created_on)) <= 20
    )
group by 1, 2
order by 1,2 desc

view: monthly_user_stats {
  derived_table: {
    sql: select date_format(dates.date, '%Y-%m') as month
      ,partner_id
      ,count(1) as num_users
      ,count(case when expiration_date > dates.date or expiration_date is null then 1 end) as num_active_users
      ,count(case when (expiration_date > last_day(dates.date - interval 1 month) or expiration_date is null)
        and date(users.created_adjusted) <= last_day(dates.date - interval 1 month) then 1 end) as last_month_active_users
from ${dates.SQL_TABLE_NAME} as dates
inner join ${users.SQL_TABLE_NAME} as users
  on (dayofmonth(dates.date + interval 1 day) = 1 or dates.date = current_date)
 and date(users.created_adjusted) <= dates.date
where
  users.user_id not in (
    select
      promos.user_id
    from
      ${promotions_table.SQL_TABLE_NAME} as promos
    where
      promos.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>', '<% parameter promo_alias2 %>')
    and (
      DATEDIFF(curdate(), DATE(users.created_on)) <= 8
      OR
      DATEDIFF(users.expiration_date, DATE(users.created_on)) <= 20
    )
 )
AND users.complementary <> 1
AND
  users.user_id not in (
    select
      user_id
    from
      ${users.SQL_TABLE_NAME} as u
    where
      datediff(u.expiration_date, date(u.created_on)) <= 20
    )
group by 1, 2
order by 1,2 desc ;;
  }

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

  dimension: partner_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.partner_id ;;
  }

  dimension: num_users_scorecard {
    sql: ${TABLE}.num_users ;;
  }

  dimension: num_active_users_scorecard {
    sql: ${TABLE}.num_active_users ;;
  }

  measure: num_users {
    type: sum
    sql: ${TABLE}.num_users ;;
  }

  measure: num_active_users {
    type: sum
    sql: ${TABLE}.num_active_users ;;
  }

  measure: num_active_users_last_month {
    type: sum
    sql: ${TABLE}.last_month_active_users ;;
  }
}

- view: monthly_user_stats
  derived_table:
    sql: select date_format(dates.date, '%Y-%m') as month
      ,partner_id
      ,count(1) as num_users
      ,count(case when expiration_date > dates.date or expiration_date is null then 1 end) as num_active_users
      ,count(case when (expiration_date > last_day(dates.date - interval 1 month) or expiration_date is null)
        and date(users.created_adjusted) <= last_day(dates.date - interval 1 month) then 1 end) as last_month_active_users
from ${dates.SQL_TABLE_NAME} as dates
inner join ${users.SQL_TABLE_NAME} as users
  on (dayofmonth(dates.date + interval 1 day) = 1 or dates.date = current_date)
 and date(users.created_adjusted) <= dates.date
where
  users.user_id not in (
    select
      promos.user_id
    from
      ${promotions_table.SQL_TABLE_NAME} as promos
    where
      promos.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>', '<% parameter promo_alias2 %>')
    and (
      DATEDIFF(curdate(), DATE(users.created_on)) <= 8
      OR
      DATEDIFF(users.expiration_date, DATE(users.created_on)) <= 20
    )
 )
AND users.complementary <> 1
AND
  users.user_id not in (
    select
      user_id
    from
      ${users.SQL_TABLE_NAME} as u
    where
      datediff(u.expiration_date, date(u.created_on)) <= 20
    )
group by 1, 2
order by 1,2 desc
  fields:
  - dimension: month
    sql: ${TABLE}.month
  - dimension: partner_id
    type: int
    primary_key: true
    sql: ${TABLE}.partner_id
  - dimension: num_users_scorecard
    sql: ${TABLE}.num_users
  - dimension: num_active_users_scorecard
    sql: ${TABLE}.num_active_users
  - measure: num_users
    type: sum
    sql: ${TABLE}.num_users
  - measure: num_active_users
    type: sum
    sql: ${TABLE}.num_active_users
  - measure: num_active_users_last_month
    type: sum
    sql: ${TABLE}.last_month_active_users