User Retention Fact

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

SELECT
  ufe.email AS email
  , du1.created_date AS day_1_activity
  , (
      SELECT COUNT(*)
      FROM {{ ref('daily_interaction') }} AS du2
      WHERE DATE_ADD(ufe.first, INTERVAL 1 day) < du2.created_date
        AND DATE_ADD(ufe.first, INTERVAL 7 day) >= du2.created_date
        AND ufe.email = du2.email
  ) AS day_2_7_active
  , (
      SELECT COUNT(*)
      FROM {{ ref('daily_interaction') }} AS du3
      WHERE DATE_ADD(ufe.first, INTERVAL 7 day) < du3.created_date
        AND DATE_ADD(ufe.first, INTERVAL 30 day) >= du3.created_date
        AND ufe.email = du3.email
  ) AS day_8_30_active
FROM {{ ref('user_first_event_fact') }} AS ufe
LEFT JOIN {{ ref('daily_interaction') }} AS du1
ON DATE_ADD(ufe.first, INTERVAL 1 day) = du1.created_date
AND ufe.email = du1.email

# models/schema.yml
version: 2

models:
  - name: user_retention_fact
    columns:
      - name: email
        tests:
          - unique
          - not_null
      - name: day_1_activity
      - name: day_2_7_active
      - name: day_8_30_active

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
  ufe.email AS email
  , du1.created_date AS day_1_activity
  , (
      SELECT COUNT(*)
      FROM ${daily_interaction.SQL_TABLE_NAME} AS du2
      WHERE DATE_ADD(ufe.first, INTERVAL 1 day) < du2.created_date
        AND DATE_ADD(ufe.first, INTERVAL 7 day) >= du2.created_date
        AND ufe.email = du2.email
  ) AS day_2_7_active
  , (
      SELECT COUNT(*)
      FROM ${daily_interaction.SQL_TABLE_NAME} AS du3
      WHERE DATE_ADD(ufe.first, INTERVAL 7 day) < du3.created_date
        AND DATE_ADD(ufe.first, INTERVAL 30 day) >= du3.created_date
        AND ufe.email = du3.email
  ) AS day_8_30_active
FROM ${user_first_event_fact.SQL_TABLE_NAME} AS ufe
LEFT JOIN ${daily_interaction.SQL_TABLE_NAME} AS du1
ON DATE_ADD(ufe.first, INTERVAL 1 day) = du1.created_date
AND ufe.email = du1.email

view: user_retention_fact {
  derived_table: {
    sql: SELECT
  ufe.email AS email
  , du1.created_date AS day_1_activity
  , (
      SELECT COUNT(*)
      FROM ${daily_interaction.SQL_TABLE_NAME} AS du2
      WHERE DATE_ADD(ufe.first, INTERVAL 1 day) < du2.created_date
        AND DATE_ADD(ufe.first, INTERVAL 7 day) >= du2.created_date
        AND ufe.email = du2.email
  ) AS day_2_7_active
  , (
      SELECT COUNT(*)
      FROM ${daily_interaction.SQL_TABLE_NAME} AS du3
      WHERE DATE_ADD(ufe.first, INTERVAL 7 day) < du3.created_date
        AND DATE_ADD(ufe.first, INTERVAL 30 day) >= du3.created_date
        AND ufe.email = du3.email
  ) AS day_8_30_active
FROM ${user_first_event_fact.SQL_TABLE_NAME} AS ufe
LEFT JOIN ${daily_interaction.SQL_TABLE_NAME} AS du1
ON DATE_ADD(ufe.first, INTERVAL 1 day) = du1.created_date
AND ufe.email = du1.email ;;
  }

  dimension: email {
    primary_key: yes
  }

  dimension: day_1_activity {
    type: yesno
    sql: ${TABLE}.day_1_activity IS NOT NULL ;;
  }

  dimension: day_2_7_active {
    type: yesno
    sql: ${TABLE}.day_2_7_active > 0 ;;
  }

  dimension: day_8_30_active {
    type: yesno
    sql: ${TABLE}.day_8_30_active > 0 ;;
  }

  measure: day_1_retention_count {
    type: count
    filters: [day_1_activity: "True"]
  }

  measure: day_2_7_active_retention_count {
    type: count
    filters: [day_2_7_active: "True"]
  }

  measure: day_8_30_active_retention_count {
    type: count
    filters: [day_8_30_active: "True"]
  }

  measure: day_1_retention_percentage {
    type: number
    sql: 100.0 * ${day_1_retention_count}/NULLIF(${users.count},0) ;;
  }

  measure: day_2_7_retention_percentage {
    type: number
    sql: 100.0 * ${day_2_7_active_retention_count}/NULLIF(${users.count},0) ;;
  }

  measure: day_8_30_retention_percentage {
    type: number
    sql: 100.0 * ${day_8_30_active_retention_count}/NULLIF(${users.count},0) ;;
  }
}

- view: user_retention_fact
  derived_table:
    sql: SELECT
  ufe.email AS email
  , du1.created_date AS day_1_activity
  , (
      SELECT COUNT(*)
      FROM ${daily_interaction.SQL_TABLE_NAME} AS du2
      WHERE DATE_ADD(ufe.first, INTERVAL 1 day) < du2.created_date
        AND DATE_ADD(ufe.first, INTERVAL 7 day) >= du2.created_date
        AND ufe.email = du2.email
  ) AS day_2_7_active
  , (
      SELECT COUNT(*)
      FROM ${daily_interaction.SQL_TABLE_NAME} AS du3
      WHERE DATE_ADD(ufe.first, INTERVAL 7 day) < du3.created_date
        AND DATE_ADD(ufe.first, INTERVAL 30 day) >= du3.created_date
        AND ufe.email = du3.email
  ) AS day_8_30_active
FROM ${user_first_event_fact.SQL_TABLE_NAME} AS ufe
LEFT JOIN ${daily_interaction.SQL_TABLE_NAME} AS du1
ON DATE_ADD(ufe.first, INTERVAL 1 day) = du1.created_date
AND ufe.email = du1.email
  fields:
  - dimension: email
    primary_key: true
  - dimension: day_1_activity
    type: yesno
    sql: ${TABLE}.day_1_activity IS NOT NULL
  - dimension: day_2_7_active
    type: yesno
    sql: ${TABLE}.day_2_7_active > 0
  - dimension: day_8_30_active
    type: yesno
    sql: ${TABLE}.day_8_30_active > 0
  - measure: day_1_retention_count
    type: count
    filters:
    - day_1_activity: True
  - measure: day_2_7_active_retention_count
    type: count
    filters:
    - day_2_7_active: True
  - measure: day_8_30_active_retention_count
    type: count
    filters:
    - day_8_30_active: True
  - measure: day_1_retention_percentage
    type: number
    sql: 100.0 * ${day_1_retention_count}/NULLIF(${users.count},0)
  - measure: day_2_7_retention_percentage
    type: number
    sql: 100.0 * ${day_2_7_active_retention_count}/NULLIF(${users.count},0)
  - measure: day_8_30_retention_percentage
    type: number
    sql: 100.0 * ${day_8_30_active_retention_count}/NULLIF(${users.count},0)