Active Subscriptions

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

with months as (
select
    greatest(date_trunc('month', dates.d), date_trunc('month',dates.d)) as month
from generate_series('2013-01-01'::date, now(), '1 day') as dates(d)
group by 1
order by 1)
select months.month, packages.name as package, partners.name as partner ,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
     when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
     when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5%' then 'CustomerType5erType3'
     else 'Other' end
     as type
from subscriptions, months, packages, partners where
((last_refill_date is null and (months.month between date(start_date) and date(start_date) + days_paid * interval '1 day')) or
(months.month between date(start_date) and date(last_refill_date) + days_paid * interval '1 day')     )
and start_date is not null and days_paid is not null and days_paid < 10000 and subscriptions.package_id = packages.id and partners.id = subscriptions.embedded_in_id

# models/schema.yml
version: 2

models:
  - name: active_subscriptions
    columns:
      - name: package
      - name: partner
      - name: type

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.

with months as (
select
    greatest(date_trunc('month', dates.d), date_trunc('month',dates.d)) as month
from generate_series('2013-01-01'::date, now(), '1 day') as dates(d)
group by 1
order by 1)
select months.month, packages.name as package, partners.name as partner ,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
     when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
     when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5%' then 'CustomerType5erType3'
     else 'Other' end
     as type
from subscriptions, months, packages, partners where
((last_refill_date is null and (months.month between date(start_date) and date(start_date) + days_paid * interval '1 day')) or
(months.month between date(start_date) and date(last_refill_date) + days_paid * interval '1 day')     )
and start_date is not null and days_paid is not null and days_paid < 10000 and subscriptions.package_id = packages.id and partners.id = subscriptions.embedded_in_id

view: active_subscriptions {
  derived_table: {
    sql: with months as (
select
    greatest(date_trunc('month', dates.d), date_trunc('month',dates.d)) as month
from generate_series('2013-01-01'::date, now(), '1 day') as dates(d)
group by 1
order by 1)
select months.month, packages.name as package, partners.name as partner ,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
     when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
     when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5%' then 'CustomerType5erType3'
     else 'Other' end
     as type
from subscriptions, months, packages, partners where
((last_refill_date is null and (months.month between date(start_date) and date(start_date) + days_paid * interval '1 day')) or
(months.month between date(start_date) and date(last_refill_date) + days_paid * interval '1 day')     )
and start_date is not null and days_paid is not null and days_paid < 10000 and subscriptions.package_id = packages.id and partners.id = subscriptions.embedded_in_id ;;
  }

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

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

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

  dimension_group: month {
    type: time
    timeframes: [month]
    sql: ${TABLE}.month ;;
  }

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

- view: active_subscriptions
  derived_table:
    sql: with months as (
select
    greatest(date_trunc('month', dates.d), date_trunc('month',dates.d)) as month
from generate_series('2013-01-01'::date, now(), '1 day') as dates(d)
group by 1
order by 1)
select months.month, packages.name as package, partners.name as partner ,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
     when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
     when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
     when packages.name like '%CustomerType5%' then 'CustomerType5erType3'
     else 'Other' end
     as type
from subscriptions, months, packages, partners where
((last_refill_date is null and (months.month between date(start_date) and date(start_date) + days_paid * interval '1 day')) or
(months.month between date(start_date) and date(last_refill_date) + days_paid * interval '1 day')     )
and start_date is not null and days_paid is not null and days_paid < 10000 and subscriptions.package_id = packages.id and partners.id = subscriptions.embedded_in_id
  fields:
  - dimension: package
    sql: ${TABLE}.package
  - dimension: partner
    sql: ${TABLE}.partner
  - dimension: type
    sql: ${TABLE}.type
  - dimension_group: month
    type: time
    timeframes:
    - month
    sql: ${TABLE}.month
  - measure: count
    type: count
    drill_fields:
    - detail*