Daily Star Accumulation

-- models/daily_star_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 daily_accum_sum
from
(
  select
    a.date_created,
    a.type,
    coalesce(b.count,0) as count
  from
  (
    select
      a.date_created,
      b.type
    from
      (select cast(created as date) as date_created from stars_star group by 1) as a,
      (select
        case
          when repository.is_private = 'yes'
          then 'Private'
          else 'Public'
        end as type
      from
        stars_star as star
      left join
        repository_table as repository
          on star.object_id = repository.id
      group by 1) as b
  ) as a
  left join
  (
    select
      cast(created as date) as date_created,
      case
        when repository.is_private = 'yes'
        then 'Private'
        else 'Public'
      end as type,
      count(*) as count
    from
      stars_star as star
    left join
      repository_table as repository
        on star.object_id = repository.id
    group by 1,2
  ) as b
    on a.date_created = b.date_created
    and a.type = b.type
) as z

# models/schema.yml
version: 2

models:
  - name: daily_star_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 daily_accum_sum
from
(
  select
    a.date_created,
    a.type,
    coalesce(b.count,0) as count
  from
  (
    select
      a.date_created,
      b.type
    from
      (select cast(created as date) as date_created from stars_star group by 1) as a,
      (select
        case
          when repository.is_private = 'yes'
          then 'Private'
          else 'Public'
        end as type
      from
        stars_star as star
      left join
        repository_table as repository
          on star.object_id = repository.id
      group by 1) as b
  ) as a
  left join
  (
    select
      cast(created as date) as date_created,
      case
        when repository.is_private = 'yes'
        then 'Private'
        else 'Public'
      end as type,
      count(*) as count
    from
      stars_star as star
    left join
      repository_table as repository
        on star.object_id = repository.id
    group by 1,2
  ) as b
    on a.date_created = b.date_created
    and a.type = b.type
) as z

view: daily_star_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 daily_accum_sum
from
(
  select
    a.date_created,
    a.type,
    coalesce(b.count,0) as count
  from
  (
    select
      a.date_created,
      b.type
    from
      (select cast(created as date) as date_created from stars_star group by 1) as a,
      (select
        case
          when repository.is_private = 'yes'
          then 'Private'
          else 'Public'
        end as type
      from
        stars_star as star
      left join
        repository_table as repository
          on star.object_id = repository.id
      group by 1) as b
  ) as a
  left join
  (
    select
      cast(created as date) as date_created,
      case
        when repository.is_private = 'yes'
        then 'Private'
        else 'Public'
      end as type,
      count(*) as count
    from
      stars_star as star
    left join
      repository_table as repository
        on star.object_id = repository.id
    group by 1,2
  ) as b
    on a.date_created = b.date_created
    and a.type = b.type
) as z ;;
  }

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

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

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

- view: daily_star_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 daily_accum_sum
from
(
  select
    a.date_created,
    a.type,
    coalesce(b.count,0) as count
  from
  (
    select
      a.date_created,
      b.type
    from
      (select cast(created as date) as date_created from stars_star group by 1) as a,
      (select
        case
          when repository.is_private = 'yes'
          then 'Private'
          else 'Public'
        end as type
      from
        stars_star as star
      left join
        repository_table as repository
          on star.object_id = repository.id
      group by 1) as b
  ) as a
  left join
  (
    select
      cast(created as date) as date_created,
      case
        when repository.is_private = 'yes'
        then 'Private'
        else 'Public'
      end as type,
      count(*) as count
    from
      stars_star as star
    left join
      repository_table as repository
        on star.object_id = repository.id
    group by 1,2
  ) as b
    on a.date_created = b.date_created
    and a.type = b.type
) as z
  fields:
  - dimension: created
    type: time
    sql: ${TABLE}.created
  - dimension: type
    sql: ${TABLE}.type
  - measure: daily_accum_sum
    type: sum
    sql: ${TABLE}.daily_accum_sum