Derived Signup Cohort Status

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

select date_format(cs.start_date_time, '%Y-%m') as start_month, count(1) as total_customers,
        sum(case when cs2.status = 1 and cs2.frequency = 1 then 1 else 0 end) as active_monthly,
        sum(case when cs2.status = 1 and cs2.frequency = 2 then 1 else 0 end) as active_eom,
        sum(case when cs2.status = 4 and p.number_of_months = 1 then 1 else 0 end) as pause_1,
        sum(case when cs2.status = 4 and p.number_of_months = 2 then 1 else 0 end) as pause_2,
        sum(case when cs2.status = 4 and p.number_of_months = 3 then 1 else 0 end) as pause_3,
        sum(case when cs2.status = 2 then 1 else 0 end) as suspended,
        sum(case when cs2.status = 0 then 1 else 0 end) as cancelled,
        sum(case when cs2.status = 3 then 1 else 0 end) as suspend_timeout
from customer_subscriptions cs
        join customer_subscriptions cs2
                on cs2.id = (select max(id) from customer_subscriptions where customer_id = cs.customer_id)
        left join customer_subscription_pauses p
                on p.id = (select max(id) from customer_subscription_pauses where customer_subscription_id = cs2.id and status = 1)
where cs.id = (select min(id) from customer_subscriptions where customer_id = cs.customer_id)
group by 1
limit 50

# models/schema.yml
version: 2

models:
  - name: derived_signup_cohort_status
    columns:
      - name: current_day
      - name: start_month
      - name: total_customers
      - name: active_monthly
      - name: active_eom
      - name: pause_1
      - name: pause_2
      - name: pause_3
      - name: suspended
      - name: cancelled
      - name: suspend_timeout

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 date_format(cs.start_date_time, '%Y-%m') as start_month, count(1) as total_customers,
        sum(case when cs2.status = 1 and cs2.frequency = 1 then 1 else 0 end) as active_monthly,
        sum(case when cs2.status = 1 and cs2.frequency = 2 then 1 else 0 end) as active_eom,
        sum(case when cs2.status = 4 and p.number_of_months = 1 then 1 else 0 end) as pause_1,
        sum(case when cs2.status = 4 and p.number_of_months = 2 then 1 else 0 end) as pause_2,
        sum(case when cs2.status = 4 and p.number_of_months = 3 then 1 else 0 end) as pause_3,
        sum(case when cs2.status = 2 then 1 else 0 end) as suspended,
        sum(case when cs2.status = 0 then 1 else 0 end) as cancelled,
        sum(case when cs2.status = 3 then 1 else 0 end) as suspend_timeout
from customer_subscriptions cs
        join customer_subscriptions cs2
                on cs2.id = (select max(id) from customer_subscriptions where customer_id = cs.customer_id)
        left join customer_subscription_pauses p
                on p.id = (select max(id) from customer_subscription_pauses where customer_subscription_id = cs2.id and status = 1)
where cs.id = (select min(id) from customer_subscriptions where customer_id = cs.customer_id)
group by 1
limit 50

view: derived_signup_cohort_status {
  derived_table: {
    sql: select date_format(cs.start_date_time, '%Y-%m') as start_month, count(1) as total_customers,
        sum(case when cs2.status = 1 and cs2.frequency = 1 then 1 else 0 end) as active_monthly,
        sum(case when cs2.status = 1 and cs2.frequency = 2 then 1 else 0 end) as active_eom,
        sum(case when cs2.status = 4 and p.number_of_months = 1 then 1 else 0 end) as pause_1,
        sum(case when cs2.status = 4 and p.number_of_months = 2 then 1 else 0 end) as pause_2,
        sum(case when cs2.status = 4 and p.number_of_months = 3 then 1 else 0 end) as pause_3,
        sum(case when cs2.status = 2 then 1 else 0 end) as suspended,
        sum(case when cs2.status = 0 then 1 else 0 end) as cancelled,
        sum(case when cs2.status = 3 then 1 else 0 end) as suspend_timeout
from customer_subscriptions cs
        join customer_subscriptions cs2
                on cs2.id = (select max(id) from customer_subscriptions where customer_id = cs.customer_id)
        left join customer_subscription_pauses p
                on p.id = (select max(id) from customer_subscription_pauses where customer_subscription_id = cs2.id and status = 1)
where cs.id = (select min(id) from customer_subscriptions where customer_id = cs.customer_id)
group by 1
limit 50 ;;
  }

  dimension: current_day {
    type: int
    sql: day(now()) ;;
  }

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

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

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

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

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

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

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

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

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

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

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

- view: derived_signup_cohort_status
  derived_table:
    sql: select date_format(cs.start_date_time, '%Y-%m') as start_month, count(1) as total_customers,
        sum(case when cs2.status = 1 and cs2.frequency = 1 then 1 else 0 end) as active_monthly,
        sum(case when cs2.status = 1 and cs2.frequency = 2 then 1 else 0 end) as active_eom,
        sum(case when cs2.status = 4 and p.number_of_months = 1 then 1 else 0 end) as pause_1,
        sum(case when cs2.status = 4 and p.number_of_months = 2 then 1 else 0 end) as pause_2,
        sum(case when cs2.status = 4 and p.number_of_months = 3 then 1 else 0 end) as pause_3,
        sum(case when cs2.status = 2 then 1 else 0 end) as suspended,
        sum(case when cs2.status = 0 then 1 else 0 end) as cancelled,
        sum(case when cs2.status = 3 then 1 else 0 end) as suspend_timeout
from customer_subscriptions cs
        join customer_subscriptions cs2
                on cs2.id = (select max(id) from customer_subscriptions where customer_id = cs.customer_id)
        left join customer_subscription_pauses p
                on p.id = (select max(id) from customer_subscription_pauses where customer_subscription_id = cs2.id and status = 1)
where cs.id = (select min(id) from customer_subscriptions where customer_id = cs.customer_id)
group by 1
limit 50
  fields:
  - dimension: current_day
    type: int
    sql: day(now())
  - dimension: start_month
    sql: ${TABLE}.start_month
  - dimension: total_customers
    type: number
    sql: ${TABLE}.total_customers
  - dimension: active_monthly
    type: number
    sql: ${TABLE}.active_monthly
  - dimension: active_eom
    type: number
    sql: ${TABLE}.active_eom
  - dimension: pause_1
    type: number
    sql: ${TABLE}.pause_1
  - dimension: pause_2
    type: number
    sql: ${TABLE}.pause_2
  - dimension: pause_3
    type: number
    sql: ${TABLE}.pause_3
  - dimension: suspended
    type: number
    sql: ${TABLE}.suspended
  - dimension: cancelled
    type: number
    sql: ${TABLE}.cancelled
  - dimension: suspend_timeout
    type: number
    sql: ${TABLE}.suspend_timeout
  - measure: count
    type: count
    drill_fields:
    - detail*