Derived Subscription Type Subscriptions

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

select p.id as partner_id, p.partner_identifier, p.name as partner_name, p.contact_email as partner_email,
    cs.id as customer_subscription_id, cs.customer_id, cs.start_date_time, o.sub_total+o.shipping_charges-o.discount as initial_order_amount
from customer_subscriptions cs
  join orders o
    on cs.customer_id = o.customer_id
      and timestampdiff(minute, cs.start_date_time,o.created_at) = 0
  join coupon_instances i
    on o.coupon_instance_id = i.id
  join partners_coupons pc
    on i.coupon_id = pc.coupon_id
  join partners p
    on pc.partner_id = p.id
WHERE (NOT EXISTS (
  select cs2.id
  from customer_subscriptions cs2
    join orders o2
      on cs2.customer_id = o2.customer_id
        and date(cs2.start_date_time) = date(o2.created_at)
    join coupon_instances i2
      on o2.coupon_instance_id = i2.id
    join coupons cp2
      on i2.coupon_id = cp2.id
        where left(cp2.code,4) in ('PartnerCode01', 'PartnerCode08', 'PartnerCode09', 'PartnerCode11', 'PartnerCode12', 'PartnerCode13', 'PartnerCode14', 'PartnerCode15')
        and cs2.start_date_time < '14/1/1'
        and cs.id = cs2.id))

# models/schema.yml
version: 2

models:
  - name: derived_subscription_type_subscriptions
    columns:
      - name: customer_id
      - name: customer_subscription_id
      - name: initial_order_amount
      - name: partner_id
      - name: partner_identifier
      - name: partner_email
      - name: partner_name

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 p.id as partner_id, p.partner_identifier, p.name as partner_name, p.contact_email as partner_email,
    cs.id as customer_subscription_id, cs.customer_id, cs.start_date_time, o.sub_total+o.shipping_charges-o.discount as initial_order_amount
from customer_subscriptions cs
  join orders o
    on cs.customer_id = o.customer_id
      and timestampdiff(minute, cs.start_date_time,o.created_at) = 0
  join coupon_instances i
    on o.coupon_instance_id = i.id
  join partners_coupons pc
    on i.coupon_id = pc.coupon_id
  join partners p
    on pc.partner_id = p.id
WHERE (NOT EXISTS (
  select cs2.id
  from customer_subscriptions cs2
    join orders o2
      on cs2.customer_id = o2.customer_id
        and date(cs2.start_date_time) = date(o2.created_at)
    join coupon_instances i2
      on o2.coupon_instance_id = i2.id
    join coupons cp2
      on i2.coupon_id = cp2.id
        where left(cp2.code,4) in ('PartnerCode01', 'PartnerCode08', 'PartnerCode09', 'PartnerCode11', 'PartnerCode12', 'PartnerCode13', 'PartnerCode14', 'PartnerCode15')
        and cs2.start_date_time < '14/1/1'
        and cs.id = cs2.id))

view: derived_subscription_type_subscriptions {
  derived_table: {
    sql: select p.id as partner_id, p.partner_identifier, p.name as partner_name, p.contact_email as partner_email,
    cs.id as customer_subscription_id, cs.customer_id, cs.start_date_time, o.sub_total+o.shipping_charges-o.discount as initial_order_amount
from customer_subscriptions cs
  join orders o
    on cs.customer_id = o.customer_id
      and timestampdiff(minute, cs.start_date_time,o.created_at) = 0
  join coupon_instances i
    on o.coupon_instance_id = i.id
  join partners_coupons pc
    on i.coupon_id = pc.coupon_id
  join partners p
    on pc.partner_id = p.id
WHERE (NOT EXISTS (
  select cs2.id
  from customer_subscriptions cs2
    join orders o2
      on cs2.customer_id = o2.customer_id
        and date(cs2.start_date_time) = date(o2.created_at)
    join coupon_instances i2
      on o2.coupon_instance_id = i2.id
    join coupons cp2
      on i2.coupon_id = cp2.id
        where left(cp2.code,4) in ('PartnerCode01', 'PartnerCode08', 'PartnerCode09', 'PartnerCode11', 'PartnerCode12', 'PartnerCode13', 'PartnerCode14', 'PartnerCode15')
        and cs2.start_date_time < '14/1/1'
        and cs.id = cs2.id)) ;;
  }

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

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

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

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

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

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

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

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

  measure: count {
    type: count
  }

  measure: count_percent_of_total {
    label: "Count (Percent of Total)"
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: sum_order_amount {
    type: sum
    sql: ${initial_order_amount} ;;
  }
}

- view: derived_subscription_type_subscriptions
  derived_table:
    sql: select p.id as partner_id, p.partner_identifier, p.name as partner_name, p.contact_email as partner_email,
    cs.id as customer_subscription_id, cs.customer_id, cs.start_date_time, o.sub_total+o.shipping_charges-o.discount as initial_order_amount
from customer_subscriptions cs
  join orders o
    on cs.customer_id = o.customer_id
      and timestampdiff(minute, cs.start_date_time,o.created_at) = 0
  join coupon_instances i
    on o.coupon_instance_id = i.id
  join partners_coupons pc
    on i.coupon_id = pc.coupon_id
  join partners p
    on pc.partner_id = p.id
WHERE (NOT EXISTS (
  select cs2.id
  from customer_subscriptions cs2
    join orders o2
      on cs2.customer_id = o2.customer_id
        and date(cs2.start_date_time) = date(o2.created_at)
    join coupon_instances i2
      on o2.coupon_instance_id = i2.id
    join coupons cp2
      on i2.coupon_id = cp2.id
        where left(cp2.code,4) in ('PartnerCode01', 'PartnerCode08', 'PartnerCode09', 'PartnerCode11', 'PartnerCode12', 'PartnerCode13', 'PartnerCode14', 'PartnerCode15')
        and cs2.start_date_time < '14/1/1'
        and cs.id = cs2.id))
  fields:
  - dimension: customer_id
    type: int
    sql: ${TABLE}.customer_id
  - dimension: customer_subscription_id
    type: int
    sql: ${TABLE}.customer_subscription_id
  - dimension: initial_order_amount
    type: number
    sql: ${TABLE}.initial_order_amount
  - dimension: partner_id
    type: int
    sql: ${TABLE}.partner_id
  - dimension: partner_identifier
    sql: ${TABLE}.partner_identifier
  - dimension: partner_email
    sql: ${TABLE}.partner_email
  - dimension: partner_name
    sql: ${TABLE}.partner_name
  - dimension_group: customer_start
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.start_date_time
  - measure: count
    type: count
  - measure: count_percent_of_total
    type: percent_of_total
    label: Count (Percent of Total)
    sql: ${count}
  - measure: sum_order_amount
    type: sum
    sql: ${initial_order_amount}