Gsv Facts

-- models/gsv_facts.sql
{{ config(materialized='table') }}

SELECT
  *
FROM (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv
LEFT JOIN (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS prev_month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS prev_core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv_prev ON gsv.month = (gsv_prev.prev_month + interval '1 year')

# models/schema.yml
version: 2

models:
  - name: gsv_facts
    columns:
      - name: month
      - name: quarter_num
      - name: quarter
      - name: prev_core_gsv
      - name: core_gsv

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
  *
FROM (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv
LEFT JOIN (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS prev_month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS prev_core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv_prev ON gsv.month = (gsv_prev.prev_month + interval '1 year')

view: gsv_facts {
  derived_table: {
    sql: SELECT
  *
FROM (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv
LEFT JOIN (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS prev_month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS prev_core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv_prev ON gsv.month = (gsv_prev.prev_month + interval '1 year') ;;
    persist_for: "4 hours"
  }

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

  dimension: quarter_num {
  }

  dimension: quarter {
    type: string
    sql: CAST(${gsv_year} || '-' || ${quarter_num} AS text) ;;
  }

  dimension: prev_core_gsv {
    type: number
    hidden: yes
    sql: ${TABLE}.prev_core_gsv ;;
  }

  dimension: core_gsv {
    type: number
    hidden: yes
    sql: ${TABLE}.core_gsv ;;
  }

  dimension_group: gsv {
    type: time
    timeframes: [month, year, month_num]
    sql: ${month} ;;
  }

  measure: core_gsv_total {
    type: sum
    sql: ${core_gsv} ;;
  }

  measure: prev_core_gsv_total {
    type: sum
    sql: ${prev_core_gsv} ;;
  }

  measure: core_gsv_yoy_growth {
    type: number
    sql: 100.0*(${core_gsv_total} - ${prev_core_gsv_total}) / NULLIF(${prev_core_gsv_total},0) ;;
  }
}

- view: gsv_facts
  derived_table:
    sql: SELECT
  *
FROM (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv
LEFT JOIN (
SELECT
  TO_DATE(a.date, 'YYYY-MM') AS prev_month
, SUM(CASE WHEN (a.dept = 'BILL' AND a.include_in_generic_stats = 'true') AND NOT COALESCE(a.employer IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>), FALSE) AND s.referencedfk IS NULL AND o.client_segment IS NULL THEN a.total_charge ELSE NULL END) AS prev_core_gsv
FROM agg.a_assignment AS a
LEFT JOIN public.sparseattributes AS s ON s.referencedfk::BIGINT = a.employer::BIGINT AND s.referencedtable='Companies' AND s.key = 'revenuetype' AND s.value = 'MANAGED_SERVICES'
LEFT JOIN "ExternalDB"."Companies" AS c ON c."Record ID#"::BIGINT = a.employer::BIGINT
LEFT JOIN finance.opportunities AS o ON a.employer::BIGINT = o.client_id::BIGINT AND o.client_segment IN ('SMB','Enterprise')
GROUP BY 1 ) AS gsv_prev ON gsv.month = (gsv_prev.prev_month + interval '1 year')
    persist_for: 4 hours
  fields:
  - dimension: month
    sql: ${TABLE}.month
  - dimension: quarter_num
  - dimension: quarter
    type: string
    sql: CAST(${gsv_year} || '-' || ${quarter_num} AS text)
  - dimension: prev_core_gsv
    type: number
    hidden: true
    sql: ${TABLE}.prev_core_gsv
  - dimension: core_gsv
    type: number
    hidden: true
    sql: ${TABLE}.core_gsv
  - dimension_group: gsv
    type: time
    timeframes:
    - month
    - year
    - month_num
    sql: ${month}
  - measure: core_gsv_total
    type: sum
    sql: ${core_gsv}
  - measure: prev_core_gsv_total
    type: sum
    sql: ${prev_core_gsv}
  - measure: core_gsv_yoy_growth
    type: number
    sql: 100.0*(${core_gsv_total} - ${prev_core_gsv_total}) / NULLIF(${prev_core_gsv_total},0)