data.ml
SQL Saas translation

Word Count Reports

Word Count Reports model for saas translation.

source-preserved

Blocks

word_count_reports.sql · 35 lines
inferred 
-- models/word_count_reports.sql
{{ config(materialized='view') }}

WITH wum AS
    (
     SELECT
          wcr.account_id
        , DATE_TRUNC('day', wcr.date_created) AS date_created
        , MAX(wcr.word_count_report_id) AS word_count_report_id
      FROM pinch_reports.word_count_reports AS wcr
      GROUP BY wcr.account_id, DATE_TRUNC('day', wcr.date_created)
     )
SELECT
    wum.word_count_report_id
  , wum.account_id
  , wum.date_created
  , wcr.word_count
FROM wum
JOIN pinch_reports.word_count_reports wcr
ON (wum.word_count_report_id = wcr.word_count_report_id)

# models/schema.yml
version: 2

models:
  - name: word_count_reports
    columns:
      - name: word_count_report_id
        tests:
          - unique
          - not_null
      - name: account_id
      - name: created
      - name: word_count
      - name: month_last_day_or_yesterday

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.
word_count_reports.sql · 17 lines
source-preserved validation: parses 
WITH wum AS
    (
     SELECT
          wcr.account_id
        , DATE_TRUNC('day', wcr.date_created) AS date_created
        , MAX(wcr.word_count_report_id) AS word_count_report_id
      FROM pinch_reports.word_count_reports AS wcr
      GROUP BY wcr.account_id, DATE_TRUNC('day', wcr.date_created)
     )
SELECT
    wum.word_count_report_id
  , wum.account_id
  , wum.date_created
  , wcr.word_count
FROM wum
JOIN pinch_reports.word_count_reports wcr
ON (wum.word_count_report_id = wcr.word_count_report_id)
word_count_reports.view.lkml · 57 lines
inferred 
view: word_count_reports {
  derived_table: {
    sql: WITH wum AS
    (
     SELECT
          wcr.account_id
        , DATE_TRUNC('day', wcr.date_created) AS date_created
        , MAX(wcr.word_count_report_id) AS word_count_report_id
      FROM pinch_reports.word_count_reports AS wcr
      GROUP BY wcr.account_id, DATE_TRUNC('day', wcr.date_created)
     )
SELECT
    wum.word_count_report_id
  , wum.account_id
  , wum.date_created
  , wcr.word_count
FROM wum
JOIN pinch_reports.word_count_reports wcr
ON (wum.word_count_report_id = wcr.word_count_report_id) ;;
  }

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

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

  dimension: created {
    type: date
    sql: ${TABLE}.date_created ;;
  }

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

  dimension: month_last_day_or_yesterday {
    label: "Show Only End of Month Values"
    type: yesno
    sql: CASE
  WHEN LAST_DAY(${TABLE}.date_created) = ${created} THEN true
  WHEN DATE(DATEADD(day,-1,GETDATE())) = ${created} THEN true
  ELSE false
END = true ;;
  }

  measure: total_word_count {
    type: sum
    sql: ${TABLE}.word_count ;;
  }
}
word_count_reports.view.lookml · 44 lines
source-preserved 
- view: word_count_reports
  derived_table:
    sql: WITH wum AS
    (
     SELECT
          wcr.account_id
        , DATE_TRUNC('day', wcr.date_created) AS date_created
        , MAX(wcr.word_count_report_id) AS word_count_report_id
      FROM pinch_reports.word_count_reports AS wcr
      GROUP BY wcr.account_id, DATE_TRUNC('day', wcr.date_created)
     )
SELECT
    wum.word_count_report_id
  , wum.account_id
  , wum.date_created
  , wcr.word_count
FROM wum
JOIN pinch_reports.word_count_reports wcr
ON (wum.word_count_report_id = wcr.word_count_report_id)
  fields:
  - dimension: word_count_report_id
    type: int
    primary_key: true
    sql: ${TABLE}.word_count_report_id
  - dimension: account_id
    type: int
    sql: ${TABLE}.account_id
  - dimension: created
    type: date
    sql: ${TABLE}.date_created
  - dimension: word_count
    type: int
    sql: ${TABLE}.word_count
  - dimension: month_last_day_or_yesterday
    type: yesno
    label: Show Only End of Month Values
    sql: CASE
  WHEN LAST_DAY(${TABLE}.date_created) = ${created} THEN true
  WHEN DATE(DATEADD(day,-1,GETDATE())) = ${created} THEN true
  ELSE false
END = true
  - measure: total_word_count
    type: sum
    sql: ${TABLE}.word_count

Contents

  • word_count_reports.sql
  • word_count_reports.sql
  • word_count_reports.view.lkml
  • word_count_reports.view.lookml