Derived Survey Lists

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

select distinct c.obfuscated_id, c.email, c.first_name, c.last_name,
f.age, f.income,
  case when f.education = 1 then 'High School'
    when f.education = 2 then 'Some College'
    when f.education = 3 then 'Bachelors'
    when f.education = 4 then 'Grad Degree'
    when f.education = 5 then 'Less than HS'
    else NULL end as education,
  f.gender, f.mosaic, a.zip_code,
  case when cs.subscription_id = 4 then 'PremiumPlan' when cs.subscription_id = 3 then 'StandardPlan' else 'BasicPlan' end as plan,
    case when cs.frequency = 1 then 'MonthlyFrequency' else 'NonStandardFrequency' end as frequency,
  cs.status, cs.start_date_time, fo.total - fo.tax as initial_ov, bg_first_purchase, bg_quantity,
  sb_first_purchase, sb_quantity, owc_first_purchase, owc_quantity,
              ps_first_purchase, ps_quantity, rs_first_purchase, rs_quantity, r.random_number
from customer_subscriptions cs
  join customers c
    on cs.customer_id = c.id
  join customer_random_numbers_dynamic_1 r
    on c.id = r.customer_id
  left join customer_cohort_members m
    on c.id = m.customer_id and m.customer_cohort_id in (117,155,156,157,158)
  join facts_customers f
    on c.id = f.customer_id
      and (f.gender is null or f.gender = 'm') and f.utm_campaign not like '%fem%' and f.utm_campaign not like '%hishers%'
  join facts_customer_orders o
    on c.id = o.customer_id
  join addresses a
    on cs.shipping_address_id = a.id
  left join orders fo
    on fo.customer_id = c.id and timestampdiff(minute, cs.start_date_time, fo.created_at) = 0
where cs.status = 1 and m.id is null

# models/schema.yml
version: 2

models:
  - name: derived_survey_lists
    columns:
      - name: obfuscated_id
      - name: first_name
      - name: last_name
      - name: email
      - name: age
      - name: income
      - name: education
      - name: gender
      - name: mosaic
      - name: zip_code
      - name: plan
      - name: frequency
      - name: status
      - name: initial_ov
      - name: random_number

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 distinct c.obfuscated_id, c.email, c.first_name, c.last_name,
f.age, f.income,
  case when f.education = 1 then 'High School'
    when f.education = 2 then 'Some College'
    when f.education = 3 then 'Bachelors'
    when f.education = 4 then 'Grad Degree'
    when f.education = 5 then 'Less than HS'
    else NULL end as education,
  f.gender, f.mosaic, a.zip_code,
  case when cs.subscription_id = 4 then 'PremiumPlan' when cs.subscription_id = 3 then 'StandardPlan' else 'BasicPlan' end as plan,
    case when cs.frequency = 1 then 'MonthlyFrequency' else 'NonStandardFrequency' end as frequency,
  cs.status, cs.start_date_time, fo.total - fo.tax as initial_ov, bg_first_purchase, bg_quantity,
  sb_first_purchase, sb_quantity, owc_first_purchase, owc_quantity,
              ps_first_purchase, ps_quantity, rs_first_purchase, rs_quantity, r.random_number
from customer_subscriptions cs
  join customers c
    on cs.customer_id = c.id
  join customer_random_numbers_dynamic_1 r
    on c.id = r.customer_id
  left join customer_cohort_members m
    on c.id = m.customer_id and m.customer_cohort_id in (117,155,156,157,158)
  join facts_customers f
    on c.id = f.customer_id
      and (f.gender is null or f.gender = 'm') and f.utm_campaign not like '%fem%' and f.utm_campaign not like '%hishers%'
  join facts_customer_orders o
    on c.id = o.customer_id
  join addresses a
    on cs.shipping_address_id = a.id
  left join orders fo
    on fo.customer_id = c.id and timestampdiff(minute, cs.start_date_time, fo.created_at) = 0
where cs.status = 1 and m.id is null

view: derived_survey_lists {
  derived_table: {
    sql: select distinct c.obfuscated_id, c.email, c.first_name, c.last_name,
f.age, f.income,
  case when f.education = 1 then 'High School'
    when f.education = 2 then 'Some College'
    when f.education = 3 then 'Bachelors'
    when f.education = 4 then 'Grad Degree'
    when f.education = 5 then 'Less than HS'
    else NULL end as education,
  f.gender, f.mosaic, a.zip_code,
  case when cs.subscription_id = 4 then 'PremiumPlan' when cs.subscription_id = 3 then 'StandardPlan' else 'BasicPlan' end as plan,
    case when cs.frequency = 1 then 'MonthlyFrequency' else 'NonStandardFrequency' end as frequency,
  cs.status, cs.start_date_time, fo.total - fo.tax as initial_ov, bg_first_purchase, bg_quantity,
  sb_first_purchase, sb_quantity, owc_first_purchase, owc_quantity,
              ps_first_purchase, ps_quantity, rs_first_purchase, rs_quantity, r.random_number
from customer_subscriptions cs
  join customers c
    on cs.customer_id = c.id
  join customer_random_numbers_dynamic_1 r
    on c.id = r.customer_id
  left join customer_cohort_members m
    on c.id = m.customer_id and m.customer_cohort_id in (117,155,156,157,158)
  join facts_customers f
    on c.id = f.customer_id
      and (f.gender is null or f.gender = 'm') and f.utm_campaign not like '%fem%' and f.utm_campaign not like '%hishers%'
  join facts_customer_orders o
    on c.id = o.customer_id
  join addresses a
    on cs.shipping_address_id = a.id
  left join orders fo
    on fo.customer_id = c.id and timestampdiff(minute, cs.start_date_time, fo.created_at) = 0
where cs.status = 1 and m.id is null ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension_group: start {
    type: time
    timeframes: [date]
    sql: ${TABLE}.start_date_time ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}

- view: derived_survey_lists
  derived_table:
    sql: select distinct c.obfuscated_id, c.email, c.first_name, c.last_name,
f.age, f.income,
  case when f.education = 1 then 'High School'
    when f.education = 2 then 'Some College'
    when f.education = 3 then 'Bachelors'
    when f.education = 4 then 'Grad Degree'
    when f.education = 5 then 'Less than HS'
    else NULL end as education,
  f.gender, f.mosaic, a.zip_code,
  case when cs.subscription_id = 4 then 'PremiumPlan' when cs.subscription_id = 3 then 'StandardPlan' else 'BasicPlan' end as plan,
    case when cs.frequency = 1 then 'MonthlyFrequency' else 'NonStandardFrequency' end as frequency,
  cs.status, cs.start_date_time, fo.total - fo.tax as initial_ov, bg_first_purchase, bg_quantity,
  sb_first_purchase, sb_quantity, owc_first_purchase, owc_quantity,
              ps_first_purchase, ps_quantity, rs_first_purchase, rs_quantity, r.random_number
from customer_subscriptions cs
  join customers c
    on cs.customer_id = c.id
  join customer_random_numbers_dynamic_1 r
    on c.id = r.customer_id
  left join customer_cohort_members m
    on c.id = m.customer_id and m.customer_cohort_id in (117,155,156,157,158)
  join facts_customers f
    on c.id = f.customer_id
      and (f.gender is null or f.gender = 'm') and f.utm_campaign not like '%fem%' and f.utm_campaign not like '%hishers%'
  join facts_customer_orders o
    on c.id = o.customer_id
  join addresses a
    on cs.shipping_address_id = a.id
  left join orders fo
    on fo.customer_id = c.id and timestampdiff(minute, cs.start_date_time, fo.created_at) = 0
where cs.status = 1 and m.id is null
  fields:
  - dimension: obfuscated_id
    sql: ${TABLE}.obfuscated_id
  - dimension: first_name
    sql: ${TABLE}.first_name
  - dimension: last_name
    sql: ${TABLE}.last_name
  - dimension: email
    sql: ${TABLE}.email
  - dimension: age
    type: int
    sql: ${TABLE}.age
  - dimension: income
    type: int
    sql: ${TABLE}.income
  - dimension: education
    sql: ${TABLE}.education
  - dimension: gender
    sql: ${TABLE}.gender
  - dimension: mosaic
    sql: ${TABLE}.mosaic
  - dimension: zip_code
    sql: ${TABLE}.zip_code
  - dimension: plan
    sql: ${TABLE}.plan
  - dimension: frequency
    sql: ${TABLE}.frequency
  - dimension: status
    type: number
    sql: ${TABLE}.status
  - dimension: initial_ov
    type: number
    sql: ${TABLE}.initial_ov
  - dimension: random_number
    type: number
    sql: ${TABLE}.random_number
  - dimension_group: start
    type: time
    timeframes:
    - date
    sql: ${TABLE}.start_date_time
  - measure: count
    type: count
    drill_fields:
    - detail*