Users Summary

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

SELECT uid as user_id
       , MAX(created_at) as created_at
       , MAX(dob) as dob
       , MAX(gender) as gender
       , MAX(push_id) as push_id
       , MAX(phone) as phone
       , MAX(marketing_opt_in) as allow_marketing
       , MAX(is_facebook) as is_facebook
       , MAX(TIMESTAMP 'epoch' + ts * INTERVAL '1 Second ') as last_seen
       , COUNT(case when action = 'purchase' then perm_name else null end) as purchases
       , COUNT(case when action = 'waitinglist' then perm_name else null end) as reservations
       , COUNT(DISTINCT(case when action = 'purchase' then perm_name else null end)) as purchased_events
       , COUNT(DISTINCT(case when action = 'waitinglist' then perm_name else null end)) as reserved_events
       , SUM(case when action = 'purchase' then paid else 0 end) as spendings
FROM ts_user_base
WHERE regexp_substr(uid, '@[^.]*') != ''
GROUP BY uid

# models/schema.yml
version: 2

models:
  - name: users_summary
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: created_at
      - name: dob_tmp
      - name: dob
      - name: age
      - name: age_tiers
      - name: gender
      - name: phone
      - name: push_id
      - name: allow_marketing
      - name: is_facebook
      - name: last_seen
      - name: purchases
      - name: reservations
      - name: purchased_events
      - name: reserved_events
      - name: lifetime_purchases_tiered
      - name: lifetime_reservations_tiered
      - name: lifetime_purchased_events_tiered
      - name: lifetime_reserved_events_tiered
      - name: spendings

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 uid as user_id
       , MAX(created_at) as created_at
       , MAX(dob) as dob
       , MAX(gender) as gender
       , MAX(push_id) as push_id
       , MAX(phone) as phone
       , MAX(marketing_opt_in) as allow_marketing
       , MAX(is_facebook) as is_facebook
       , MAX(TIMESTAMP 'epoch' + ts * INTERVAL '1 Second ') as last_seen
       , COUNT(case when action = 'purchase' then perm_name else null end) as purchases
       , COUNT(case when action = 'waitinglist' then perm_name else null end) as reservations
       , COUNT(DISTINCT(case when action = 'purchase' then perm_name else null end)) as purchased_events
       , COUNT(DISTINCT(case when action = 'waitinglist' then perm_name else null end)) as reserved_events
       , SUM(case when action = 'purchase' then paid else 0 end) as spendings
FROM ts_user_base
WHERE regexp_substr(uid, '@[^.]*') != ''
GROUP BY uid

view: users_summary {
  derived_table: {
    sql: SELECT uid as user_id
       , MAX(created_at) as created_at
       , MAX(dob) as dob
       , MAX(gender) as gender
       , MAX(push_id) as push_id
       , MAX(phone) as phone
       , MAX(marketing_opt_in) as allow_marketing
       , MAX(is_facebook) as is_facebook
       , MAX(TIMESTAMP 'epoch' + ts * INTERVAL '1 Second ') as last_seen
       , COUNT(case when action = 'purchase' then perm_name else null end) as purchases
       , COUNT(case when action = 'waitinglist' then perm_name else null end) as reservations
       , COUNT(DISTINCT(case when action = 'purchase' then perm_name else null end)) as purchased_events
       , COUNT(DISTINCT(case when action = 'waitinglist' then perm_name else null end)) as reserved_events
       , SUM(case when action = 'purchase' then paid else 0 end) as spendings
FROM ts_user_base
WHERE regexp_substr(uid, '@[^.]*') != ''
GROUP BY uid ;;
  }

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

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

  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: floor(datediff(days, ${dob}, current_date)/365) ;;
  }

  dimension: age_tiers {
    type: tier
    sql: ${age} ;;
  }

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

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

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

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

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

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

  dimension: purchases {
    label: "Tickets Purchased"
    type: number
    sql: ${TABLE}.purchases ;;
  }

  dimension: reservations {
    label: "Wait List Tickets"
    type: number
    sql: ${TABLE}.reservations ;;
  }

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

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

  dimension: lifetime_purchases_tiered {
    type: tier
    sql: ${purchases} ;;
  }

  dimension: lifetime_reservations_tiered {
    type: tier
    sql: ${reservations} ;;
  }

  dimension: lifetime_purchased_events_tiered {
    type: tier
    sql: ${purchased_events} ;;
  }

  dimension: lifetime_reserved_events_tiered {
    type: tier
    sql: ${reserved_events} ;;
  }

  dimension: spendings {
    label: "£ Spent"
    type: number
    value_format: "\"£\"#,##0"
    sql: ${TABLE}.spendings / 100.0 ;;
  }

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

  measure: count_purchases {
    label: "Count Tickets Purchased"
    type: count_distinct
    drill_fields: [user_aggregation_drill_down_overview*]
    sql: ${TABLE}.purchases ;;
  }

  measure: count_waitlist {
    label: "Count Waitlist Reserved"
    type: count_distinct
    sql: ${TABLE}.reservations ;;
  }

  measure: total_tickets_purchased {
    label: "Total Tickets Purchased"
    type: count_distinct
    sql: ${TABLE}.purchases ;;
  }

  measure: total_events_reserved {
    label: "Total Events Wait List"
    type: count_distinct
    sql: ${TABLE}.reserved_events ;;
  }
}

- view: users_summary
  derived_table:
    sql: SELECT uid as user_id
       , MAX(created_at) as created_at
       , MAX(dob) as dob
       , MAX(gender) as gender
       , MAX(push_id) as push_id
       , MAX(phone) as phone
       , MAX(marketing_opt_in) as allow_marketing
       , MAX(is_facebook) as is_facebook
       , MAX(TIMESTAMP 'epoch' + ts * INTERVAL '1 Second ') as last_seen
       , COUNT(case when action = 'purchase' then perm_name else null end) as purchases
       , COUNT(case when action = 'waitinglist' then perm_name else null end) as reservations
       , COUNT(DISTINCT(case when action = 'purchase' then perm_name else null end)) as purchased_events
       , COUNT(DISTINCT(case when action = 'waitinglist' then perm_name else null end)) as reserved_events
       , SUM(case when action = 'purchase' then paid else 0 end) as spendings
FROM ts_user_base
WHERE regexp_substr(uid, '@[^.]*') != ''
GROUP BY uid
  fields:
  - dimension: user_id
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: created_at
    type: time
    sql: ${TABLE}.created_at
  - 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: floor(datediff(days, ${dob}, current_date)/365)
  - dimension: age_tiers
    type: tier
    sql: ${age}
  - dimension: gender
    type: string
    sql: ${TABLE}.gender
  - dimension: phone
    type: string
    sql: ${TABLE}.phone
  - dimension: push_id
    type: string
    sql: ${TABLE}.push_id
  - dimension: allow_marketing
    type: yesno
    sql: case when ${TABLE}.allow_marketing = 'True' then TRUE else FALSE end
  - dimension: is_facebook
    type: yesno
    sql: case when ${TABLE}.is_facebook = 'True' then TRUE else FALSE end
  - dimension: last_seen
    type: time
    sql: ${TABLE}.last_seen
  - dimension: purchases
    type: number
    label: Tickets Purchased
    sql: ${TABLE}.purchases
  - dimension: reservations
    type: number
    label: Wait List Tickets
    sql: ${TABLE}.reservations
  - dimension: purchased_events
    type: number
    sql: ${TABLE}.purchased_events
  - dimension: reserved_events
    type: number
    sql: ${TABLE}.reserved_events
  - dimension: lifetime_purchases_tiered
    type: tier
    sql: ${purchases}
  - dimension: lifetime_reservations_tiered
    type: tier
    sql: ${reservations}
  - dimension: lifetime_purchased_events_tiered
    type: tier
    sql: ${purchased_events}
  - dimension: lifetime_reserved_events_tiered
    type: tier
    sql: ${reserved_events}
  - dimension: spendings
    type: number
    label: £ Spent
    value_format: '"£"#,##0'
    sql: ${TABLE}.spendings / 100.0
  - measure: count_users
    type: count_distinct
    drill_fields:
    - user_aggregation_drill_down_overview*
    sql: ${TABLE}.user_id
  - measure: count_purchases
    type: count_distinct
    label: Count Tickets Purchased
    drill_fields:
    - user_aggregation_drill_down_overview*
    sql: ${TABLE}.purchases
  - measure: count_waitlist
    type: count_distinct
    label: Count Waitlist Reserved
    sql: ${TABLE}.reservations
  - measure: total_tickets_purchased
    type: count_distinct
    label: Total Tickets Purchased
    sql: ${TABLE}.purchases
  - measure: total_events_reserved
    type: count_distinct
    label: Total Events Wait List
    sql: ${TABLE}.reserved_events