Users

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

select cu.*
      ,case when (promotions_table.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>','<% parameter promo_alias2 %>'))
        then date_add(date(pu.created_on), interval 8 day) else date(pu.created_on) end as created_adjusted
      ,pu.id
      ,pu.created_on as account_created_on
      ,pu.type
      ,pu.partner_id
      ,pu.status
      ,pu.complementary
      ,pu.activated_on
      ,pu.expiration_date
      ,w.zip
from users_table pu
left join venue_users cu on cu.user_id = pu.user_id
left join user_promotion user on cu.user_id = user.user_id
left join promotion_msa promotions_table on user.id = promotions_table.id
left join waitlist w on cu.email_id = w.email
where pu.type = 'company'

# models/schema.yml
version: 2

models:
  - name: users
    columns:
      - name: passport_id
      - name: user_id
        tests:
          - unique
          - not_null
      - name: first_name
      - name: last_name
      - name: email
      - name: gender
      - name: login_count
      - name: created_adjusted
      - name: activated_date
      - name: expiration
      - name: zip
      - name: refund_exclusion
      - name: partner_id
      - name: status
      - name: complementary
      - name: has_expired
      - name: reservation_cancellation_type
      - name: reservation_status
      - name: month_1
      - name: month_2

Assumptions

• 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 cu.*
      ,case when (promotions_table.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>','<% parameter promo_alias2 %>'))
        then date_add(date(pu.created_on), interval 8 day) else date(pu.created_on) end as created_adjusted
      ,pu.id
      ,pu.created_on as account_created_on
      ,pu.type
      ,pu.partner_id
      ,pu.status
      ,pu.complementary
      ,pu.activated_on
      ,pu.expiration_date
      ,w.zip
from users_table pu
left join venue_users cu on cu.user_id = pu.user_id
left join user_promotion user on cu.user_id = user.user_id
left join promotion_msa promotions_table on user.id = promotions_table.id
left join waitlist w on cu.email_id = w.email
where pu.type = 'company'

view: users {
  derived_table: {
    sql: select cu.*
      ,case when (promotions_table.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>','<% parameter promo_alias2 %>'))
        then date_add(date(pu.created_on), interval 8 day) else date(pu.created_on) end as created_adjusted
      ,pu.id
      ,pu.created_on as account_created_on
      ,pu.type
      ,pu.partner_id
      ,pu.status
      ,pu.complementary
      ,pu.activated_on
      ,pu.expiration_date
      ,w.zip
from users_table pu
left join venue_users cu on cu.user_id = pu.user_id
left join user_promotion user on cu.user_id = user.user_id
left join promotion_msa promotions_table on user.id = promotions_table.id
left join waitlist w on cu.email_id = w.email
where pu.type = 'company' ;;
  }

  dimension: passport_id {
    type: int
    hidden: yes
    sql: ${TABLE}.id ;;
  }

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

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

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

  dimension: email {
    sql: ${TABLE}.email_id ;;
  }

  dimension: gender {
    sql: case
  when ${TABLE}.gender = 'Unspecified' or ${TABLE}.gender is null then 'Unspecified'
  else ${TABLE}.gender
end ;;
  }

  dimension: login_count {
    type: number
    sql: ${TABLE}.login_count ;;
  }

  dimension: created_adjusted {
    type: time
    sql: ${TABLE}.created_adjusted ;;
  }

  dimension: activated_date {
    type: date
    sql: ${TABLE}.activated_on ;;
  }

  dimension: expiration {
    type: time
    sql: ${TABLE}.expiration_date ;;
  }

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

  dimension: refund_exclusion {
    type: yesno
    sql: DATEDIFF(${expiration_date}, DATE(${created_date})) <= 20 ;;
  }

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

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

  dimension: complementary {
    type: yesno
    sql: ${TABLE}.complementary ;;
  }

  dimension: has_expired {
    type: yesno
    sql: ${expiration_date} <= DATE(NOW()) ;;
  }

  dimension: reservation_cancellation_type {
    type: yesno
    sql: (${user_reservations.cancellation_type} in ('user_late_cancel', 'missed_class', 'missed_class_bookable') or ${user_reservations.cancellation_type} is null) ;;
  }

  dimension: reservation_status {
    type: yesno
    sql: (${user_reservations.status} != 'failed') ;;
  }

  dimension: month_1 {
    type: yesno
    sql: ((ROUND(DATEDIFF(${expiration_date}, ${created_adjusted_date})/30.42,0) >= 1 OR ${expiration_date} is NULL) AND ceil(datediff(now(), ${created_adjusted_date})/30.42) >=1) ;;
  }

  dimension: month_2 {
    type: yesno
    sql: ((ROUND(DATEDIFF(${expiration_date}, ${created_adjusted_date})/30.42,0) >= 2 OR ${expiration_date} is NULL) AND ceil(datediff(now(), ${created_adjusted_date})/30.42) >=2) ;;
  }

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

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

  measure: count {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${TABLE}.user_id ;;
  }

  measure: month_1_sum {
    type: count
    filters: [month_1: "True"]
  }

  measure: month_2_sum {
    type: count
    filters: [month_2: "True"]
  }

  measure: non_<% parameter promo_trial %>_ads {
    type: count
    filters: [created_adjusted_date: "this month"]
    filters: [<% parameter promo_trial %>_url: "False"]
    filters: [complementary: "False"]
  }

  measure: <% parameter promo_trial %>_conversion {
    type: number
    value_format: "0.0%"
    sql: ${<% parameter promo_trial %>_users_converts}/NULLIF(${<% parameter promo_trial %>_user}, 0) ;;
  }

  measure: classes_per_<% parameter promo_trial %> {
    type: number
    value_format: "0.0"
    sql: ${<% parameter promo_trial %>_class_count}/NULLIF(${<% parameter promo_trial %>_user}, 0) ;;
  }

  measure: count_expired_regular {
    type: count
    filters: [has_expired: "True"]
    filters: [expiration_date: "this month"]
    filters: [complementary: "False"]
    filters: [<% parameter promo_trial %>_url: "False"]
  }

  measure: churned_this_month {
    type: number
    sql: ${count_expired_regular} + ${count_expired_<% parameter promo_trial %>} ;;
  }

  measure: gross_adds {
    label: "Gross Adds This Montn"
    type: number
    sql: ${non_<% parameter promo_trial %>_ads} + ${<% parameter promo_trial %>_ads} ;;
  }

  measure: net_adds {
    label: "Net Adds This Month"
    type: number
    sql: ${gross_adds} - ${churned_this_month} ;;
  }

  measure: mom_percent_growth {
    label: "M-o-M Percent Growth"
    type: number
    value_format: "0.0%"
    sql: ${user_count_month_to_date} / NULLIF(${last_month_count_actives}, 0)-1 ;;
  }

  measure: percent_churn {
    label: "Percent Churn"
    type: number
    value_format: "0.0%"
    sql: ${churned_this_month}/NULLIF(${last_month_count_actives}, 0) ;;
  }

  measure: last_month_count_actives {
    label: "User Count Last Month"
    type: max
    filters: [created_adjusted_date: "last month"]
    sql: ${monthly_user_stats.num_active_users_scorecard} ;;
  }

  measure: cumulative_count_active {
    type: max
    sql: ${monthly_user_stats.num_active_users_scorecard} ;;
  }

  measure: user_count_month_to_date {
    label: "User Count Month To Date"
    type: number
    sql: case when ${last_month_count_actives} is null then ${net_adds} else ${last_month_count_actives} + ${net_adds} end ;;
  }

  measure: number_active_users_test {
    type: number
    sql: ${monthly_user_stats.num_active_users} ;;
  }

  measure: number_active_users_last_month {
    type: number
    sql: ${monthly_user_stats.num_active_users_last_month} ;;
  }

  measure: total_growth {
    type: number
    value_format: "0.0%"
    sql: (${number_active_users_test}/${number_active_users_last_month}) -1 ;;
  }

  measure: share_percent_of_users_weekly {
    description: "The number of users created that week for the medium category category from the waitlist divided by the total number of users created that week."
    type: number
    value_format: "0.0%"
    sql: ${users.count}/${weekly_user_count.total_users} ;;
  }

  measure: share_percent_of_users_overall {
    description: "The number of users created that weekfrom the waitlist divided by the total number of users created that week."
    type: number
    value_format: "0.0%"
    sql: ${users.count}/${weekly_user_count.total_users} ;;
  }
}

- view: users
  derived_table:
    sql: select cu.*
      ,case when (promotions_table.alias in ('<% parameter promo_trial %>','<% parameter promo_take3 %>','<% parameter promo_alias1 %>','<% parameter promo_alias2 %>'))
        then date_add(date(pu.created_on), interval 8 day) else date(pu.created_on) end as created_adjusted
      ,pu.id
      ,pu.created_on as account_created_on
      ,pu.type
      ,pu.partner_id
      ,pu.status
      ,pu.complementary
      ,pu.activated_on
      ,pu.expiration_date
      ,w.zip
from users_table pu
left join venue_users cu on cu.user_id = pu.user_id
left join user_promotion user on cu.user_id = user.user_id
left join promotion_msa promotions_table on user.id = promotions_table.id
left join waitlist w on cu.email_id = w.email
where pu.type = 'company'
  fields:
  - dimension: passport_id
    type: int
    hidden: true
    sql: ${TABLE}.id
  - dimension: user_id
    type: int
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: first_name
    sql: ${TABLE}.first_name
  - dimension: last_name
    sql: ${TABLE}.last_name
  - dimension: email
    sql: ${TABLE}.email_id
  - dimension: gender
    sql: case
  when ${TABLE}.gender = 'Unspecified' or ${TABLE}.gender is null then 'Unspecified'
  else ${TABLE}.gender
end
  - dimension: login_count
    type: number
    sql: ${TABLE}.login_count
  - dimension: created_adjusted
    type: time
    sql: ${TABLE}.created_adjusted
  - dimension: activated_date
    type: date
    sql: ${TABLE}.activated_on
  - dimension: expiration
    type: time
    sql: ${TABLE}.expiration_date
  - dimension: zip
    sql: ${TABLE}.zip
  - dimension: refund_exclusion
    type: yesno
    sql: DATEDIFF(${expiration_date}, DATE(${created_date})) <= 20
  - dimension: partner_id
    type: int
    sql: ${TABLE}.partner_id
  - dimension: status
    sql: ${TABLE}.status
  - dimension: complementary
    type: yesno
    sql: ${TABLE}.complementary
  - dimension: has_expired
    type: yesno
    sql: ${expiration_date} <= DATE(NOW())
  - dimension: reservation_cancellation_type
    type: yesno
    sql: (${user_reservations.cancellation_type} in ('user_late_cancel', 'missed_class', 'missed_class_bookable') or ${user_reservations.cancellation_type} is null)
  - dimension: reservation_status
    type: yesno
    sql: (${user_reservations.status} != 'failed')
  - dimension: month_1
    type: yesno
    sql: ((ROUND(DATEDIFF(${expiration_date}, ${created_adjusted_date})/30.42,0) >= 1 OR ${expiration_date} is NULL) AND ceil(datediff(now(), ${created_adjusted_date})/30.42) >=1)
  - dimension: month_2
    type: yesno
    sql: ((ROUND(DATEDIFF(${expiration_date}, ${created_adjusted_date})/30.42,0) >= 2 OR ${expiration_date} is NULL) AND ceil(datediff(now(), ${created_adjusted_date})/30.42) >=2)
  - dimension_group: last_logged_on
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.last_logged_on
  - dimension_group: created
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.account_created_on
  - measure: count
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${TABLE}.user_id
  - measure: month_1_sum
    type: count
    filters:
    - month_1: True
  - measure: month_2_sum
    type: count
    filters:
    - month_2: True
  - measure: non_<% parameter promo_trial %>_ads
    type: count
    filters:
    - created_adjusted_date: this month
    - <% parameter promo_trial %>_url: False
    - complementary: False
  - measure: <% parameter promo_trial %>_conversion
    type: number
    value_format: '0.0%'
    sql: ${<% parameter promo_trial %>_users_converts}/NULLIF(${<% parameter promo_trial %>_user}, 0)
  - measure: classes_per_<% parameter promo_trial %>
    type: number
    value_format: 0.0
    sql: ${<% parameter promo_trial %>_class_count}/NULLIF(${<% parameter promo_trial %>_user}, 0)
  - measure: count_expired_regular
    type: count
    filters:
    - has_expired: True
    - expiration_date: this month
    - complementary: False
    - <% parameter promo_trial %>_url: False
  - measure: churned_this_month
    type: number
    sql: ${count_expired_regular} + ${count_expired_<% parameter promo_trial %>}
  - measure: gross_adds
    type: number
    label: Gross Adds This Montn
    sql: ${non_<% parameter promo_trial %>_ads} + ${<% parameter promo_trial %>_ads}
  - measure: net_adds
    type: number
    label: Net Adds This Month
    sql: ${gross_adds} - ${churned_this_month}
  - measure: mom_percent_growth
    type: number
    label: M-o-M Percent Growth
    value_format: '0.0%'
    sql: ${user_count_month_to_date} / NULLIF(${last_month_count_actives}, 0)-1
  - measure: percent_churn
    type: number
    label: Percent Churn
    value_format: '0.0%'
    sql: ${churned_this_month}/NULLIF(${last_month_count_actives}, 0)
  - measure: last_month_count_actives
    type: max
    label: User Count Last Month
    filters:
    - created_adjusted_date: last month
    sql: ${monthly_user_stats.num_active_users_scorecard}
  - measure: cumulative_count_active
    type: max
    sql: ${monthly_user_stats.num_active_users_scorecard}
  - measure: user_count_month_to_date
    type: number
    label: User Count Month To Date
    sql: case when ${last_month_count_actives} is null then ${net_adds} else ${last_month_count_actives} + ${net_adds} end
  - measure: number_active_users_test
    type: number
    sql: ${monthly_user_stats.num_active_users}
  - measure: number_active_users_last_month
    type: number
    sql: ${monthly_user_stats.num_active_users_last_month}
  - measure: total_growth
    type: number
    value_format: '0.0%'
    sql: (${number_active_users_test}/${number_active_users_last_month}) -1
  - measure: share_percent_of_users_weekly
    type: number
    description: The number of users created that week for the medium category category from the waitlist divided by the total number of users created that week.
    value_format: '0.0%'
    sql: ${users.count}/${weekly_user_count.total_users}
  - measure: share_percent_of_users_overall
    type: number
    description: The number of users created that weekfrom the waitlist divided by the total number of users created that week.
    value_format: '0.0%'
    sql: ${users.count}/${weekly_user_count.total_users}