Application User Facts

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

SELECT
  application_sessions.user_id AS user_id,
  --date_trunc('day', application_sessions.session_start_at) sess_start,
  CASE

    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 28) THEN 'L28'
    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 56) THEN 'L56'
    ELSE 'L84'
  END AS LSWITCH,
  count(distinct date_trunc('day', application_sessions.session_start_at))
FROM
  {{ ref('application_sessions') }} AS application_sessions

WHERE
1=1
AND (current_date - date_trunc('day', application_sessions.session_start_at) <= 84)
GROUP BY 1,2
ORDER BY 2 DESC

# models/schema.yml
version: 2

models:
  - name: application_user_facts
    columns:
      - name: user_id
      - name: lswitch
      - name: count_of_days_logged_in

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
  application_sessions.user_id AS user_id,
  --date_trunc('day', application_sessions.session_start_at) sess_start,
  CASE

    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 28) THEN 'L28'
    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 56) THEN 'L56'
    ELSE 'L84'
  END AS LSWITCH,
  count(distinct date_trunc('day', application_sessions.session_start_at))
FROM
  ${application_sessions.SQL_TABLE_NAME} AS application_sessions

WHERE
1=1
AND (current_date - date_trunc('day', application_sessions.session_start_at) <= 84)
GROUP BY 1,2
ORDER BY 2 DESC

view: application_user_facts {
  derived_table: {
    sql: SELECT
  application_sessions.user_id AS user_id,
  --date_trunc('day', application_sessions.session_start_at) sess_start,
  CASE

    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 28) THEN 'L28'
    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 56) THEN 'L56'
    ELSE 'L84'
  END AS LSWITCH,
  count(distinct date_trunc('day', application_sessions.session_start_at))
FROM
  ${application_sessions.SQL_TABLE_NAME} AS application_sessions

WHERE
1=1
AND (current_date - date_trunc('day', application_sessions.session_start_at) <= 84)
GROUP BY 1,2
ORDER BY 2 DESC ;;
  }

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

  dimension: lswitch {
    type: string
    sql: ${TABLE}.lswitch ;;
  }

  dimension: count_of_days_logged_in {
    type: int
    sql: ${TABLE}.count ;;
  }

  measure: count_of_users {
    type: count_distinct
    drill_fields: [application_sessions.user_id, application_sessions.session_start_at]
    sql: ${user_id} ;;
  }
}

- view: application_user_facts
  derived_table:
    sql: SELECT
  application_sessions.user_id AS user_id,
  --date_trunc('day', application_sessions.session_start_at) sess_start,
  CASE

    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 28) THEN 'L28'
    WHEN (current_date - date_trunc('day', application_sessions.session_start_at) <= 56) THEN 'L56'
    ELSE 'L84'
  END AS LSWITCH,
  count(distinct date_trunc('day', application_sessions.session_start_at))
FROM
  ${application_sessions.SQL_TABLE_NAME} AS application_sessions

WHERE
1=1
AND (current_date - date_trunc('day', application_sessions.session_start_at) <= 84)
GROUP BY 1,2
ORDER BY 2 DESC
  fields:
  - dimension: user_id
    type: string
    sql: ${TABLE}.user_id
  - dimension: lswitch
    type: string
    sql: ${TABLE}.lswitch
  - dimension: count_of_days_logged_in
    type: int
    sql: ${TABLE}.count
  - measure: count_of_users
    type: count_distinct
    drill_fields:
    - application_sessions.user_id
    - application_sessions.session_start_at
    sql: ${user_id}