Promoters With Users

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

SELECT *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
      start_date
    , uid as user_email
    , action
    , ts
    , perm_name as event_perm_name
    , dob
    , phone
    , marketing_opt_in
    , push_id
    , gender
FROM ts_user_base
WHERE (action = 'purchase' OR action = 'waitinglist')
) AS user_base
ON events.perm_name = user_base.event_perm_name

# models/schema.yml
version: 2

models:
  - name: promoters_with_users
    columns:
      - name: _id
      - name: created_at
      - name: updated_at
      - name: promoter_first_name
      - name: promoter_last_name
      - name: email
      - name: owned_event_id
      - name: event_id
      - name: event_created_at
      - name: perm_name
      - name: name
      - name: card_fee
      - name: currency
      - name: venue
      - name: venue_address
      - name: customer_email
      - name: fan_email
      - name: action
      - name: event_perm_name
      - name: dob_tmp
      - name: dob
      - name: age
      - name: phone
      - name: fan_phone_number
      - name: allow_marketing
      - name: push_id
      - name: gender

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 *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
      start_date
    , uid as user_email
    , action
    , ts
    , perm_name as event_perm_name
    , dob
    , phone
    , marketing_opt_in
    , push_id
    , gender
FROM ts_user_base
WHERE (action = 'purchase' OR action = 'waitinglist')
) AS user_base
ON events.perm_name = user_base.event_perm_name

view: promoters_with_users {
  derived_table: {
    sql: SELECT *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
      start_date
    , uid as user_email
    , action
    , ts
    , perm_name as event_perm_name
    , dob
    , phone
    , marketing_opt_in
    , push_id
    , gender
FROM ts_user_base
WHERE (action = 'purchase' OR action = 'waitinglist')
) AS user_base
ON events.perm_name = user_base.event_perm_name ;;
  }

  dimension: _id {
    type: string
    hidden: yes
    sql: ${TABLE}._id ;;
  }

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

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

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

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

  dimension: email {
    label: "Promoter Email"
    type: string
    sql: ${TABLE}.email ;;
  }

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

  dimension: event_id {
    type: string
    hidden: yes
    sql: ${TABLE}.event_id ;;
  }

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

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

  dimension: name {
    label: "Event Name"
    type: string
    sql: ${TABLE}.name ;;
  }

  dimension: card_fee {
    type: number
    sql: ${TABLE} card_fee ;;
  }

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

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

  dimension: venue_address {
    type: string
    sql: ${TABLE}.address ;;
  }

  dimension: customer_email {
    type: string
    sql: ${TABLE}.user_email ;;
  }

  dimension: fan_email {
    type: string
    sql: case when ${allow_marketing}
then ${customer_email}
else 'Not allowed'
end ;;
  }

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

  dimension: event_perm_name {
    type: number
    hidden: yes
    sql: ${TABLE}.event_perm_name ;;
  }

  dimension: dob_tmp {
    type: string
    hidden: yes
    sql: case when (${TABLE}.dob != 'Thu 23 Jan' AND ${TABLE}.dob != 'Sat 20 Jan')
then ${TABLE}.dob
else '0001/01/01' end ;;
  }

  dimension: dob {
    type: date
    sql: to_date(${dob_tmp}, 'DD/MM/YYYY') ;;
  }

  dimension: age {
    type: number
    sql: datediff(years, ${dob}, GETDATE()) ;;
  }

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

  dimension: fan_phone_number {
    type: string
    sql: case when ${allow_marketing}
then ${phone}
else 'Not allowed'
end ;;
  }

  dimension: allow_marketing {
    type: yesno
    sql: case when ${TABLE}.marketing_opt_in = 'True' then TRUE else FALSE end ;;
  }

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

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

  dimension_group: ts {
    type: time
    timeframes: [time, date, week, month, raw]
    sql: TIMESTAMP 'epoch' + ${TABLE}.ts * INTERVAL '1 Second ' ;;
  }

  measure: count_purchases {
    type: count
    filters: [action: "purchase"]
  }

  measure: count_waitinglist {
    type: count
  }

  measure: count_customers {
    type: count_distinct
    sql: ${customer_email} ;;
  }

  measure: count_waitlist_customers {
    type: count_distinct
    sql: ${customer_email} ;;
  }

  measure: average_age {
    type: avg
    sql: ${age} ;;
  }
}

- view: promoters_with_users
  derived_table:
    sql: SELECT *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
      start_date
    , uid as user_email
    , action
    , ts
    , perm_name as event_perm_name
    , dob
    , phone
    , marketing_opt_in
    , push_id
    , gender
FROM ts_user_base
WHERE (action = 'purchase' OR action = 'waitinglist')
) AS user_base
ON events.perm_name = user_base.event_perm_name
  fields:
  - dimension: _id
    type: string
    hidden: true
    sql: ${TABLE}._id
  - dimension: created_at
    type: time
    sql: ${TABLE}.created_at
  - dimension: updated_at
    type: time
    sql: ${TABLE}.updated_at
  - dimension: promoter_first_name
    type: string
    sql: ${TABLE}.promoter_first_name
  - dimension: promoter_last_name
    type: string
    sql: ${TABLE}.promoter_last_name
  - dimension: email
    type: string
    label: Promoter Email
    sql: ${TABLE}.email
  - dimension: owned_event_id
    type: string
    sql: ${TABLE}.owned_event_id
  - dimension: event_id
    type: string
    hidden: true
    sql: ${TABLE}.event_id
  - dimension: event_created_at
    type: time
    sql: ${TABLE}.event_created_at
  - dimension: perm_name
    type: string
    sql: ${TABLE}.perm_name
  - dimension: name
    type: string
    label: Event Name
    sql: ${TABLE}.name
  - dimension: card_fee
    type: number
    sql: ${TABLE} card_fee
  - dimension: currency
    type: number
    sql: ${TABLE}.currency
  - dimension: venue
    type: string
    sql: ${TABLE}.venue
  - dimension: venue_address
    type: string
    sql: ${TABLE}.address
  - dimension: customer_email
    type: string
    sql: ${TABLE}.user_email
  - dimension: fan_email
    type: string
    sql: case when ${allow_marketing}
then ${customer_email}
else 'Not allowed'
end
  - dimension: action
    type: string
    sql: ${TABLE}.action
  - dimension: event_perm_name
    type: number
    hidden: true
    sql: ${TABLE}.event_perm_name
  - dimension: dob_tmp
    type: string
    hidden: true
    sql: case when (${TABLE}.dob != 'Thu 23 Jan' AND ${TABLE}.dob != 'Sat 20 Jan')
then ${TABLE}.dob
else '0001/01/01' end
  - dimension: dob
    type: date
    sql: to_date(${dob_tmp}, 'DD/MM/YYYY')
  - dimension: age
    type: number
    sql: datediff(years, ${dob}, GETDATE())
  - dimension: phone
    type: string
    sql: ${TABLE}.phone
  - dimension: fan_phone_number
    type: string
    sql: case when ${allow_marketing}
then ${phone}
else 'Not allowed'
end
  - dimension: allow_marketing
    type: yesno
    sql: case when ${TABLE}.marketing_opt_in = 'True' then TRUE else FALSE end
  - dimension: push_id
    type: string
    sql: ${TABLE}.push_id
  - dimension: gender
    type: string
    sql: ${TABLE}.gender
  - dimension_group: ts
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - raw
    sql: TIMESTAMP 'epoch' + ${TABLE}.ts * INTERVAL '1 Second '
  - measure: count_purchases
    type: count
    filters:
    - action: purchase
  - measure: count_waitinglist
    type: count
  - measure: count_customers
    type: count_distinct
    sql: ${customer_email}
  - measure: count_waitlist_customers
    type: count_distinct
    sql: ${customer_email}
  - measure: average_age
    type: avg
    sql: ${age}