User Engagement

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

SELECT
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7
  ELSE end_time_30 END as day,
  distinct_id as distinct_id,
  count(*) c
FROM log_out e
RIGHT JOIN {{ ref('date_table') }}
ON
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN
    my_date(time) >= start_time AND my_date(time) < end_time_7
  ELSE my_date(time) >= start_time AND my_date(time) < end_time_30
  END

WHERE e.game_total_distance >= 2 and {% condition country_code %} mp_country_code {% endcondition %}
AND
CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7 < now() ELSE end_time_30 < now() END
GROUP BY day, distinct_id

# models/schema.yml
version: 2

models:
  - name: user_engagement
    columns:
      - name: distinct_id
      - name: count_days

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
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7
  ELSE end_time_30 END as day,
  distinct_id as distinct_id,
  count(*) c
FROM log_out e
RIGHT JOIN ${date_table.SQL_TABLE_NAME}
ON
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN
    my_date(time) >= start_time AND my_date(time) < end_time_7
  ELSE my_date(time) >= start_time AND my_date(time) < end_time_30
  END

WHERE e.game_total_distance >= 2 and {% condition country_code %} mp_country_code {% endcondition %}
AND
CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7 < now() ELSE end_time_30 < now() END
GROUP BY day, distinct_id

view: user_engagement {
  derived_table: {
    sql: SELECT
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7
  ELSE end_time_30 END as day,
  distinct_id as distinct_id,
  count(*) c
FROM log_out e
RIGHT JOIN ${date_table.SQL_TABLE_NAME}
ON
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN
    my_date(time) >= start_time AND my_date(time) < end_time_7
  ELSE my_date(time) >= start_time AND my_date(time) < end_time_30
  END

WHERE e.game_total_distance >= 2 and {% condition country_code %} mp_country_code {% endcondition %}
AND
CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7 < now() ELSE end_time_30 < now() END
GROUP BY day, distinct_id ;;
  }

  dimension: distinct_id {
  }

  dimension: count_days {
    type: int
    sql: ${TABLE}.c ;;
  }

  dimension_group: ride {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.day ;;
  }

  measure: active_users {
    type: count
  }

  measure: 1_ride_users {
    type: count
    filters: [count_days: ">= 1"]
  }

  measure: 2_rides_users {
    type: count
    filters: [count_days: ">= 2"]
  }

  measure: 3_rides_users {
    type: count
    filters: [count_days: ">= 3"]
  }

  measure: 4_rides_users {
    type: count
    filters: [count_days: ">= 4"]
  }
}

- view: user_engagement
  derived_table:
    sql: SELECT
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7
  ELSE end_time_30 END as day,
  distinct_id as distinct_id,
  count(*) c
FROM log_out e
RIGHT JOIN ${date_table.SQL_TABLE_NAME}
ON
  CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN
    my_date(time) >= start_time AND my_date(time) < end_time_7
  ELSE my_date(time) >= start_time AND my_date(time) < end_time_30
  END

WHERE e.game_total_distance >= 2 and {% condition country_code %} mp_country_code {% endcondition %}
AND
CASE WHEN {% parameter number_of_trailing_days %} = '7' THEN end_time_7 < now() ELSE end_time_30 < now() END
GROUP BY day, distinct_id
  fields:
  - dimension: distinct_id
  - dimension: count_days
    type: int
    sql: ${TABLE}.c
  - dimension_group: ride
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.day
  - measure: active_users
    type: count
  - measure: 1_ride_users
    type: count
    filters:
    - count_days: '>= 1'
  - measure: 2_rides_users
    type: count
    filters:
    - count_days: '>= 2'
  - measure: 3_rides_users
    type: count
    filters:
    - count_days: '>= 3'
  - measure: 4_rides_users
    type: count
    filters:
    - count_days: '>= 4'