Invites Facts

-- models/invites_facts.sql
{{ config(materialized='table') }}

select
d.date
,d.prev_date
,(
  select sum(e.value) from analytics_events e
  where 1=1
    and e.eventable_type='User'
    and e.event_type='User invitations granted'
    and e.date::date+interval '1 day' < d.date
    and e.date::date+interval '1 day' >=d.prev_date
) as invites_issued
,(
  select count(*) from analytics_invites i
  where 1=1
    and i.created_at::date+interval '1 day' < d.date
    and i.created_at::date+interval '1 day' >=d.prev_date
) as invites_sent
,ic.invites_claimed
from (
  select date(d.date) as date
  ,lag(d.date) over( order by d.date)::date as prev_date
  from analytics_deliveries d
  where 1=1
    and d.date<(current_date + interval '7 days')
) d
left join (
  select count(*)invites_claimed, d.date
  from analytics_coupon_uses cu
  join analytics_user_coupons uc on uc.id=cu.user_coupon_id
  join analytics_coupons c on c.id=uc.coupon_id
  join analytics_invites i on i.coupon_id=c.id
  join analytics_orders o on o.id=cu.order_id
  join analytics_delivery_windows dw on dw.id=o.delivery_window_id
  join analytics_deliveries d on d.id=dw.delivery_id
  where 1=1
    and (o.is_paid or o.status='Scheduled')
  group by d.date
) ic on d.prev_date=ic.date

# models/schema.yml
version: 2

models:
  - name: invites_facts
    columns:
      - name: invite
      - name: dummy_pivot
      - name: invites_issued_dimension
      - name: invites_sent_dimension
      - name: invites_claimed_dimension

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

select
d.date
,d.prev_date
,(
  select sum(e.value) from analytics_events e
  where 1=1
    and e.eventable_type='User'
    and e.event_type='User invitations granted'
    and e.date::date+interval '1 day' < d.date
    and e.date::date+interval '1 day' >=d.prev_date
) as invites_issued
,(
  select count(*) from analytics_invites i
  where 1=1
    and i.created_at::date+interval '1 day' < d.date
    and i.created_at::date+interval '1 day' >=d.prev_date
) as invites_sent
,ic.invites_claimed
from (
  select date(d.date) as date
  ,lag(d.date) over( order by d.date)::date as prev_date
  from analytics_deliveries d
  where 1=1
    and d.date<(current_date + interval '7 days')
) d
left join (
  select count(*)invites_claimed, d.date
  from analytics_coupon_uses cu
  join analytics_user_coupons uc on uc.id=cu.user_coupon_id
  join analytics_coupons c on c.id=uc.coupon_id
  join analytics_invites i on i.coupon_id=c.id
  join analytics_orders o on o.id=cu.order_id
  join analytics_delivery_windows dw on dw.id=o.delivery_window_id
  join analytics_deliveries d on d.id=dw.delivery_id
  where 1=1
    and (o.is_paid or o.status='Scheduled')
  group by d.date
) ic on d.prev_date=ic.date

view: invites_facts {
  derived_table: {
    sql: select
d.date
,d.prev_date
,(
  select sum(e.value) from analytics_events e
  where 1=1
    and e.eventable_type='User'
    and e.event_type='User invitations granted'
    and e.date::date+interval '1 day' < d.date
    and e.date::date+interval '1 day' >=d.prev_date
) as invites_issued
,(
  select count(*) from analytics_invites i
  where 1=1
    and i.created_at::date+interval '1 day' < d.date
    and i.created_at::date+interval '1 day' >=d.prev_date
) as invites_sent
,ic.invites_claimed
from (
  select date(d.date) as date
  ,lag(d.date) over( order by d.date)::date as prev_date
  from analytics_deliveries d
  where 1=1
    and d.date<(current_date + interval '7 days')
) d
left join (
  select count(*)invites_claimed, d.date
  from analytics_coupon_uses cu
  join analytics_user_coupons uc on uc.id=cu.user_coupon_id
  join analytics_coupons c on c.id=uc.coupon_id
  join analytics_invites i on i.coupon_id=c.id
  join analytics_orders o on o.id=cu.order_id
  join analytics_delivery_windows dw on dw.id=o.delivery_window_id
  join analytics_deliveries d on d.id=dw.delivery_id
  where 1=1
    and (o.is_paid or o.status='Scheduled')
  group by d.date
) ic on d.prev_date=ic.date ;;
    persist_for: "12 hours"
  }

  dimension: invite {
    type: time
    sql: ${TABLE}.prev_date ;;
  }

  dimension: dummy_pivot {
    label: "x"
    sql: case when 1=1 then null end ;;
  }

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

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

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

  measure: invites_issued {
    type: max
    sql: ${invites_issued_dimension} ;;
  }

  measure: invites_sent {
    type: max
    sql: ${invites_sent_dimension} ;;
  }

  measure: invites_claimed {
    type: max
    sql: ${invites_claimed_dimension} ;;
  }
}

- view: invites_facts
  derived_table:
    sql: select
d.date
,d.prev_date
,(
  select sum(e.value) from analytics_events e
  where 1=1
    and e.eventable_type='User'
    and e.event_type='User invitations granted'
    and e.date::date+interval '1 day' < d.date
    and e.date::date+interval '1 day' >=d.prev_date
) as invites_issued
,(
  select count(*) from analytics_invites i
  where 1=1
    and i.created_at::date+interval '1 day' < d.date
    and i.created_at::date+interval '1 day' >=d.prev_date
) as invites_sent
,ic.invites_claimed
from (
  select date(d.date) as date
  ,lag(d.date) over( order by d.date)::date as prev_date
  from analytics_deliveries d
  where 1=1
    and d.date<(current_date + interval '7 days')
) d
left join (
  select count(*)invites_claimed, d.date
  from analytics_coupon_uses cu
  join analytics_user_coupons uc on uc.id=cu.user_coupon_id
  join analytics_coupons c on c.id=uc.coupon_id
  join analytics_invites i on i.coupon_id=c.id
  join analytics_orders o on o.id=cu.order_id
  join analytics_delivery_windows dw on dw.id=o.delivery_window_id
  join analytics_deliveries d on d.id=dw.delivery_id
  where 1=1
    and (o.is_paid or o.status='Scheduled')
  group by d.date
) ic on d.prev_date=ic.date
    persist_for: 12 hours
  fields:
  - dimension: invite
    type: time
    sql: ${TABLE}.prev_date
  - dimension: dummy_pivot
    label: x
    sql: case when 1=1 then null end
  - dimension: invites_issued_dimension
    type: number
    hidden: true
    sql: ${TABLE}.invites_issued
  - dimension: invites_sent_dimension
    type: number
    hidden: true
    sql: ${TABLE}.invites_sent
  - dimension: invites_claimed_dimension
    type: number
    hidden: true
    sql: ${TABLE}.invites_claimed
  - measure: invites_issued
    type: max
    sql: ${invites_issued_dimension}
  - measure: invites_sent
    type: max
    sql: ${invites_sent_dimension}
  - measure: invites_claimed
    type: max
    sql: ${invites_claimed_dimension}