Weekly Subscriptions Accumulation

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

select
  z.date_created as created,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_created rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    z.date_created,
    z.type,
    coalesce(y.count,0) as count
  from
  (
    select
      a.date_created,
      b.name as type
    from
    (
      select
        cast(date_trunc('week',created) as date) as date_created
      from
        billing_subscription
      group by 1
    ) as a
    cross join
    (
      select
        name
      from
        billing_plan
      group by 1
    ) as b
  ) as z
  left join
  (
    select
      cast(date_trunc('week',a.created) as date) as date_created,
      b.name as type,
      count(*) as count
    from
      billing_subscription as a
    left join
      billing_plan as b
        on a.plan_id = b.id
    group by 1,2
  ) as y
    on z.date_created = y.date_created
    and z.type = y.type
) as z

# models/schema.yml
version: 2

models:
  - name: weekly_subscriptions_accumulation
    columns:
      - name: created
      - 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.

select
  z.date_created as created,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_created rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    z.date_created,
    z.type,
    coalesce(y.count,0) as count
  from
  (
    select
      a.date_created,
      b.name as type
    from
    (
      select
        cast(date_trunc('week',created) as date) as date_created
      from
        billing_subscription
      group by 1
    ) as a
    cross join
    (
      select
        name
      from
        billing_plan
      group by 1
    ) as b
  ) as z
  left join
  (
    select
      cast(date_trunc('week',a.created) as date) as date_created,
      b.name as type,
      count(*) as count
    from
      billing_subscription as a
    left join
      billing_plan as b
        on a.plan_id = b.id
    group by 1,2
  ) as y
    on z.date_created = y.date_created
    and z.type = y.type
) as z

view: weekly_subscriptions_accumulation {
  derived_table: {
    sql: select
  z.date_created as created,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_created rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    z.date_created,
    z.type,
    coalesce(y.count,0) as count
  from
  (
    select
      a.date_created,
      b.name as type
    from
    (
      select
        cast(date_trunc('week',created) as date) as date_created
      from
        billing_subscription
      group by 1
    ) as a
    cross join
    (
      select
        name
      from
        billing_plan
      group by 1
    ) as b
  ) as z
  left join
  (
    select
      cast(date_trunc('week',a.created) as date) as date_created,
      b.name as type,
      count(*) as count
    from
      billing_subscription as a
    left join
      billing_plan as b
        on a.plan_id = b.id
    group by 1,2
  ) as y
    on z.date_created = y.date_created
    and z.type = y.type
) as z ;;
  }

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

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

  measure: weekly_accum_sum {
    type: sum
    sql: ${TABLE}.weekly_accum_sum ;;
  }
}

- view: weekly_subscriptions_accumulation
  derived_table:
    sql: select
  z.date_created as created,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_created rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    z.date_created,
    z.type,
    coalesce(y.count,0) as count
  from
  (
    select
      a.date_created,
      b.name as type
    from
    (
      select
        cast(date_trunc('week',created) as date) as date_created
      from
        billing_subscription
      group by 1
    ) as a
    cross join
    (
      select
        name
      from
        billing_plan
      group by 1
    ) as b
  ) as z
  left join
  (
    select
      cast(date_trunc('week',a.created) as date) as date_created,
      b.name as type,
      count(*) as count
    from
      billing_subscription as a
    left join
      billing_plan as b
        on a.plan_id = b.id
    group by 1,2
  ) as y
    on z.date_created = y.date_created
    and z.type = y.type
) as z
  fields:
  - dimension: created
    type: time
    sql: ${TABLE}.created
  - dimension: type
    sql: ${TABLE}.type
  - measure: weekly_accum_sum
    type: sum
    sql: ${TABLE}.weekly_accum_sum