SQL Saas translation

Growth Rates Wum

Growth Rates Wum model for saas translation.

source-preserved

Blocks

dbt SQL LKML LookML

growth_rates_wum.sql · 41 lines

inferred

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

WITH wum AS
  (
   SELECT wcr.account_id
     , EXTRACT(year FROM wcr.date_created) as year_of_quarter
     , EXTRACT(quarter FROM wcr.date_created) as number_of_quarter
     , EXTRACT(year FROM wcr.date_created) || '-Quarter' || EXTRACT(quarter FROM wcr.date_created) AS wum_quarter
     , MAX(wcr.word_count_report_id) AS word_count_report_id
   FROM pinch_reports.word_count_reports AS wcr
   GROUP BY wcr.account_id, year_of_quarter, number_of_quarter, wum_quarter
  )
SELECT wum.account_id
  , wum.year_of_quarter
  , wum.number_of_quarter
  , wum.wum_quarter
  , wc.word_count
  , CASE
      WHEN (lag(wum.account_id) over (order by wum.account_id, wum.wum_quarter asc)) = wum.account_id
         THEN 100.0 * wc.word_count/lag(NULLIF(wc.word_count, 0)) over (order by wum.account_id, wum.wum_quarter asc) - 100
      ELSE null
    END AS wum_growth_percentage
FROM wum
  INNER JOIN pinch_reports.word_count_reports wc
  ON wc.word_count_report_id = wum.word_count_report_id

# models/schema.yml
version: 2

models:
  - name: growth_rates_wum
    columns:
      - name: word_count_report_id
      - name: account_id
      - name: year_of_quarter
      - name: number_of_quarter
      - name: wum_quarter
      - name: word_count
      - name: growth_percentage
      - name: last_quarter

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.

growth_rates_wum.sql · 23 lines

source-preserved validation: parses

WITH wum AS
  (
   SELECT wcr.account_id
     , EXTRACT(year FROM wcr.date_created) as year_of_quarter
     , EXTRACT(quarter FROM wcr.date_created) as number_of_quarter
     , EXTRACT(year FROM wcr.date_created) || '-Quarter' || EXTRACT(quarter FROM wcr.date_created) AS wum_quarter
     , MAX(wcr.word_count_report_id) AS word_count_report_id
   FROM pinch_reports.word_count_reports AS wcr
   GROUP BY wcr.account_id, year_of_quarter, number_of_quarter, wum_quarter
  )
SELECT wum.account_id
  , wum.year_of_quarter
  , wum.number_of_quarter
  , wum.wum_quarter
  , wc.word_count
  , CASE
      WHEN (lag(wum.account_id) over (order by wum.account_id, wum.wum_quarter asc)) = wum.account_id
         THEN 100.0 * wc.word_count/lag(NULLIF(wc.word_count, 0)) over (order by wum.account_id, wum.wum_quarter asc) - 100
      ELSE null
    END AS wum_growth_percentage
FROM wum
  INNER JOIN pinch_reports.word_count_reports wc
  ON wc.word_count_report_id = wum.word_count_report_id

growth_rates_wum.view.lkml · 71 lines

inferred

view: growth_rates_wum {
  derived_table: {
    sql: WITH wum AS
  (
   SELECT wcr.account_id
     , EXTRACT(year FROM wcr.date_created) as year_of_quarter
     , EXTRACT(quarter FROM wcr.date_created) as number_of_quarter
     , EXTRACT(year FROM wcr.date_created) || '-Quarter' || EXTRACT(quarter FROM wcr.date_created) AS wum_quarter
     , MAX(wcr.word_count_report_id) AS word_count_report_id
   FROM pinch_reports.word_count_reports AS wcr
   GROUP BY wcr.account_id, year_of_quarter, number_of_quarter, wum_quarter
  )
SELECT wum.account_id
  , wum.year_of_quarter
  , wum.number_of_quarter
  , wum.wum_quarter
  , wc.word_count
  , CASE
      WHEN (lag(wum.account_id) over (order by wum.account_id, wum.wum_quarter asc)) = wum.account_id
         THEN 100.0 * wc.word_count/lag(NULLIF(wc.word_count, 0)) over (order by wum.account_id, wum.wum_quarter asc) - 100
      ELSE null
    END AS wum_growth_percentage
FROM wum
  INNER JOIN pinch_reports.word_count_reports wc
  ON wc.word_count_report_id = wum.word_count_report_id ;;
  }

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

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

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

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

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

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

  dimension: growth_percentage {
    label: " Growth Rates percentage"
    type: number
    sql: ${TABLE}.wum_growth_percentage ;;
  }

  dimension: last_quarter {
    label: "Show Only Last Quarter"
    type: yesno
    sql: CASE
  WHEN ${TABLE}.year_of_quarter = EXTRACT(year FROM getDate()) and ${TABLE}.number_of_quarter = EXTRACT(quarter FROM getDate()) THEN true
  ELSE false
  END = true ;;
  }
}

growth_rates_wum.view.lookml · 54 lines

source-preserved

- view: growth_rates_wum
  derived_table:
    sql: WITH wum AS
  (
   SELECT wcr.account_id
     , EXTRACT(year FROM wcr.date_created) as year_of_quarter
     , EXTRACT(quarter FROM wcr.date_created) as number_of_quarter
     , EXTRACT(year FROM wcr.date_created) || '-Quarter' || EXTRACT(quarter FROM wcr.date_created) AS wum_quarter
     , MAX(wcr.word_count_report_id) AS word_count_report_id
   FROM pinch_reports.word_count_reports AS wcr
   GROUP BY wcr.account_id, year_of_quarter, number_of_quarter, wum_quarter
  )
SELECT wum.account_id
  , wum.year_of_quarter
  , wum.number_of_quarter
  , wum.wum_quarter
  , wc.word_count
  , CASE
      WHEN (lag(wum.account_id) over (order by wum.account_id, wum.wum_quarter asc)) = wum.account_id
         THEN 100.0 * wc.word_count/lag(NULLIF(wc.word_count, 0)) over (order by wum.account_id, wum.wum_quarter asc) - 100
      ELSE null
    END AS wum_growth_percentage
FROM wum
  INNER JOIN pinch_reports.word_count_reports wc
  ON wc.word_count_report_id = wum.word_count_report_id
  fields:
  - dimension: word_count_report_id
    type: int
    sql: ${TABLE}.word_count_report_id
  - dimension: account_id
    type: int
    sql: ${TABLE}.account_id
  - dimension: year_of_quarter
    type: int
    sql: ${TABLE}.year_of_quarter
  - dimension: number_of_quarter
    type: int
    sql: ${TABLE}.number_of_quarter
  - dimension: wum_quarter
    sql: ${TABLE}.wum_quarter
  - dimension: word_count
    type: int
    sql: ${TABLE}.word_count
  - dimension: growth_percentage
    type: number
    label: ' Growth Rates percentage'
    sql: ${TABLE}.wum_growth_percentage
  - dimension: last_quarter
    type: yesno
    label: Show Only Last Quarter
    sql: CASE
  WHEN ${TABLE}.year_of_quarter = EXTRACT(year FROM getDate()) and ${TABLE}.number_of_quarter = EXTRACT(quarter FROM getDate()) THEN true
  ELSE false
  END = true

growth_rates_wum.sql
growth_rates_wum.sql
growth_rates_wum.view.lkml
growth_rates_wum.view.lookml

Growth Rates Wum

Blocks

Assumptions

Contents