Count Dashboard

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

select
  a.month_num,
  a.year,
  a.repo_count,
  sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding) as test,
  (a.repo_count - sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding)) / nullif(sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding),0) as pct_mom_change
from
(
  SELECT
    EXTRACT(MONTH FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS month_num,
    EXTRACT(YEAR FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS year,
    COUNT(*) AS repo_count
  FROM
    repository_table AS repository
  WHERE
    (repository.status = 1)
  AND
    repository.date_registered < ((( DATE_TRUNC('month', DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles')) + (0 || ' month')::INTERVAL) AT TIME ZONE 'America/Los_Angeles'))
  GROUP BY 1,2
) as a
order by 2,1

# models/schema.yml
version: 2

models:
  - name: count_dashboard
    columns:
      - name: month_num
      - name: year
      - name: prev_month_repo_count

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
  a.month_num,
  a.year,
  a.repo_count,
  sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding) as test,
  (a.repo_count - sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding)) / nullif(sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding),0) as pct_mom_change
from
(
  SELECT
    EXTRACT(MONTH FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS month_num,
    EXTRACT(YEAR FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS year,
    COUNT(*) AS repo_count
  FROM
    repository_table AS repository
  WHERE
    (repository.status = 1)
  AND
    repository.date_registered < ((( DATE_TRUNC('month', DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles')) + (0 || ' month')::INTERVAL) AT TIME ZONE 'America/Los_Angeles'))
  GROUP BY 1,2
) as a
order by 2,1

view: count_dashboard {
  derived_table: {
    sql: select
  a.month_num,
  a.year,
  a.repo_count,
  sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding) as test,
  (a.repo_count - sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding)) / nullif(sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding),0) as pct_mom_change
from
(
  SELECT
    EXTRACT(MONTH FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS month_num,
    EXTRACT(YEAR FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS year,
    COUNT(*) AS repo_count
  FROM
    repository_table AS repository
  WHERE
    (repository.status = 1)
  AND
    repository.date_registered < ((( DATE_TRUNC('month', DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles')) + (0 || ' month')::INTERVAL) AT TIME ZONE 'America/Los_Angeles'))
  GROUP BY 1,2
) as a
order by 2,1 ;;
  }

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

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

  dimension: prev_month_repo_count {
    hidden: yes
    sql: ${TABLE}.test ;;
  }

  measure: count {
    type: count
  }

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

  measure: pct_mom_change {
    type: sum
    sql: 100 * ${TABLE}.pct_mom_change ;;
  }
}

- view: count_dashboard
  derived_table:
    sql: select
  a.month_num,
  a.year,
  a.repo_count,
  sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding) as test,
  (a.repo_count - sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding)) / nullif(sum(a.repo_count) over (order by a.year, a.month_num rows between 1 preceding and 1 preceding),0) as pct_mom_change
from
(
  SELECT
    EXTRACT(MONTH FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS month_num,
    EXTRACT(YEAR FROM (repository.date_registered)::timestamptz AT TIME ZONE 'America/Los_Angeles') AS year,
    COUNT(*) AS repo_count
  FROM
    repository_table AS repository
  WHERE
    (repository.status = 1)
  AND
    repository.date_registered < ((( DATE_TRUNC('month', DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles')) + (0 || ' month')::INTERVAL) AT TIME ZONE 'America/Los_Angeles'))
  GROUP BY 1,2
) as a
order by 2,1
  fields:
  - dimension: month_num
    type: number
    sql: ${TABLE}.month_num
  - dimension: year
    type: number
    sql: ${TABLE}.year
  - dimension: prev_month_repo_count
    hidden: true
    sql: ${TABLE}.test
  - measure: count
    type: count
  - measure: repo_count
    type: sum
    sql: ${TABLE}.repo_count
  - measure: pct_mom_change
    type: sum
    sql: 100 * ${TABLE}.pct_mom_change