Weekly Repo Accumulation

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

select
  z.date_registered as registered,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_registered rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    cast(date_trunc('week',date_registered) as date) as date_registered,
    case
      when is_private = 'yes'
      then 'Private'
      else 'Public'
    end as type,
    count(*) as count
  from
    repository_table
  where
    status = 1
  and
    date_registered < cast(current_date as date) - cast(date_part('dow',cast(current_date as date)) as integer)
  group by 1,2
) as z

# models/schema.yml
version: 2

models:
  - name: weekly_repo_accumulation
    columns:
      - name: registered
      - 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_registered as registered,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_registered rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    cast(date_trunc('week',date_registered) as date) as date_registered,
    case
      when is_private = 'yes'
      then 'Private'
      else 'Public'
    end as type,
    count(*) as count
  from
    repository_table
  where
    status = 1
  and
    date_registered < cast(current_date as date) - cast(date_part('dow',cast(current_date as date)) as integer)
  group by 1,2
) as z

view: weekly_repo_accumulation {
  derived_table: {
    sql: select
  z.date_registered as registered,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_registered rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    cast(date_trunc('week',date_registered) as date) as date_registered,
    case
      when is_private = 'yes'
      then 'Private'
      else 'Public'
    end as type,
    count(*) as count
  from
    repository_table
  where
    status = 1
  and
    date_registered < cast(current_date as date) - cast(date_part('dow',cast(current_date as date)) as integer)
  group by 1,2
) as z ;;
  }

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

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

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

- view: weekly_repo_accumulation
  derived_table:
    sql: select
  z.date_registered as registered,
  z.type,
  sum(z.count) over (partition by z.type order by z.date_registered rows between unbounded preceding and current row) as weekly_accum_sum
from
(
  select
    cast(date_trunc('week',date_registered) as date) as date_registered,
    case
      when is_private = 'yes'
      then 'Private'
      else 'Public'
    end as type,
    count(*) as count
  from
    repository_table
  where
    status = 1
  and
    date_registered < cast(current_date as date) - cast(date_part('dow',cast(current_date as date)) as integer)
  group by 1,2
) as z
  fields:
  - dimension: registered
    type: time
    sql: ${TABLE}.registered
  - dimension: type
    sql: ${TABLE}.type
  - measure: weekly_accum_sum
    type: sum
    sql: ${TABLE}.weekly_accum_sum