User Monthly Activity

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

SELECT
  {{ ref('userstatus') }}.UserId as UserId
  , DATE_FORMAT({{ ref('userstatus') }}.firstleaguedate,'%Y-%m-01') as firstpaid_month
  , month_list.activity_month as activity_month
  , COALESCE(data.monthly_visits, 0) as monthly_visits

FROM
  {{ ref('userstatus') }}

LEFT JOIN
  (
    SELECT
    DISTINCT(DATE_FORMAT(CreatedDate,'%Y-%m-01')) as activity_month
    FROM UserRegistration
  ) as month_list
ON DATE_FORMAT({{ ref('userstatus') }}.firstleaguedate,'%Y-%m-01') <= month_list.activity_month

LEFT JOIN
  (
    SELECT
          UserId
        , DATE_FORMAT(CreatedAt,'%Y-%m-01') as activity_month
        , COUNT(distinct id) AS monthly_visits
        , SUM(Amount) as Monthly_entryfees
    FROM  {{ ref('entry_fees') }}
    GROUP BY 1,2
  ) as data
ON data.activity_month = month_list.activity_month
AND data.UserId = {{ ref('userstatus') }}.UserId

# models/schema.yml
version: 2

models:
  - name: user_monthly_activity
    columns:
      - name: userid
      - name: firstleaguemonth
      - name: activity
      - name: monthly_paidleagues
      - name: months_since_firstleaguejoin

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
  ${userstatus.SQL_TABLE_NAME}.UserId as UserId
  , DATE_FORMAT(${userstatus.SQL_TABLE_NAME}.firstleaguedate,'%Y-%m-01') as firstpaid_month
  , month_list.activity_month as activity_month
  , COALESCE(data.monthly_visits, 0) as monthly_visits

FROM
  ${userstatus.SQL_TABLE_NAME}

LEFT JOIN
  (
    SELECT
    DISTINCT(DATE_FORMAT(CreatedDate,'%Y-%m-01')) as activity_month
    FROM UserRegistration
  ) as month_list
ON DATE_FORMAT(${userstatus.SQL_TABLE_NAME}.firstleaguedate,'%Y-%m-01') <= month_list.activity_month

LEFT JOIN
  (
    SELECT
          UserId
        , DATE_FORMAT(CreatedAt,'%Y-%m-01') as activity_month
        , COUNT(distinct id) AS monthly_visits
        , SUM(Amount) as Monthly_entryfees
    FROM  ${entry_fees.SQL_TABLE_NAME}
    GROUP BY 1,2
  ) as data
ON data.activity_month = month_list.activity_month
AND data.UserId = ${userstatus.SQL_TABLE_NAME}.UserId

view: user_monthly_activity {
  derived_table: {
    sql: SELECT
  ${userstatus.SQL_TABLE_NAME}.UserId as UserId
  , DATE_FORMAT(${userstatus.SQL_TABLE_NAME}.firstleaguedate,'%Y-%m-01') as firstpaid_month
  , month_list.activity_month as activity_month
  , COALESCE(data.monthly_visits, 0) as monthly_visits

FROM
  ${userstatus.SQL_TABLE_NAME}

LEFT JOIN
  (
    SELECT
    DISTINCT(DATE_FORMAT(CreatedDate,'%Y-%m-01')) as activity_month
    FROM UserRegistration
  ) as month_list
ON DATE_FORMAT(${userstatus.SQL_TABLE_NAME}.firstleaguedate,'%Y-%m-01') <= month_list.activity_month

LEFT JOIN
  (
    SELECT
          UserId
        , DATE_FORMAT(CreatedAt,'%Y-%m-01') as activity_month
        , COUNT(distinct id) AS monthly_visits
        , SUM(Amount) as Monthly_entryfees
    FROM  ${entry_fees.SQL_TABLE_NAME}
    GROUP BY 1,2
  ) as data
ON data.activity_month = month_list.activity_month
AND data.UserId = ${userstatus.SQL_TABLE_NAME}.UserId ;;
  }

  dimension: userid {
    sql: ${TABLE}.UserId ;;
  }

  dimension: firstleaguemonth {
    label: "First League Join Month"
    type: time
    sql: ${TABLE}.firstpaid_month ;;
  }

  dimension: activity {
    type: time
    sql: ${TABLE}.activity_month ;;
  }

  dimension: monthly_paidleagues {
    label: "Monthly Paid Leagues"
    type: number
    sql: ${TABLE}.monthly_visits ;;
  }

  dimension: months_since_firstleaguejoin {
    type: number
    sql: TIMESTAMPDIFF(MONTH, ${firstleaguemonth_raw}, ${activity_raw}) ;;
  }

  measure: total_users {
    type: count_distinct
    sql: ${userid} ;;
  }

  measure: active_users {
    type: count_distinct
    filters: [monthly_paidleagues: ">0"]
    sql: ${userid} ;;
  }

  measure: percent_of_cohort_still_active {
    type: number
    value_format: "#.0\%"
    sql: 100.0 * ${active_users} / nullif(${total_users},0) ;;
  }
}

- view: user_monthly_activity
  derived_table:
    sql: SELECT
  ${userstatus.SQL_TABLE_NAME}.UserId as UserId
  , DATE_FORMAT(${userstatus.SQL_TABLE_NAME}.firstleaguedate,'%Y-%m-01') as firstpaid_month
  , month_list.activity_month as activity_month
  , COALESCE(data.monthly_visits, 0) as monthly_visits

FROM
  ${userstatus.SQL_TABLE_NAME}

LEFT JOIN
  (
    SELECT
    DISTINCT(DATE_FORMAT(CreatedDate,'%Y-%m-01')) as activity_month
    FROM UserRegistration
  ) as month_list
ON DATE_FORMAT(${userstatus.SQL_TABLE_NAME}.firstleaguedate,'%Y-%m-01') <= month_list.activity_month

LEFT JOIN
  (
    SELECT
          UserId
        , DATE_FORMAT(CreatedAt,'%Y-%m-01') as activity_month
        , COUNT(distinct id) AS monthly_visits
        , SUM(Amount) as Monthly_entryfees
    FROM  ${entry_fees.SQL_TABLE_NAME}
    GROUP BY 1,2
  ) as data
ON data.activity_month = month_list.activity_month
AND data.UserId = ${userstatus.SQL_TABLE_NAME}.UserId
  fields:
  - dimension: userid
    sql: ${TABLE}.UserId
  - dimension: firstleaguemonth
    type: time
    label: First League Join Month
    sql: ${TABLE}.firstpaid_month
  - dimension: activity
    type: time
    sql: ${TABLE}.activity_month
  - dimension: monthly_paidleagues
    type: number
    label: Monthly Paid Leagues
    sql: ${TABLE}.monthly_visits
  - dimension: months_since_firstleaguejoin
    type: number
    sql: TIMESTAMPDIFF(MONTH, ${firstleaguemonth_raw}, ${activity_raw})
  - measure: total_users
    type: count_distinct
    sql: ${userid}
  - measure: active_users
    type: count_distinct
    filters:
    - monthly_paidleagues: '>0'
    sql: ${userid}
  - measure: percent_of_cohort_still_active
    type: number
    value_format: '#.0\%'
    sql: 100.0 * ${active_users} / nullif(${total_users},0)