dbt Retail gastronomy

Sales Yoy

Sales Yoy model for retail gastronomy.

inferred

Blocks

dbt SQL LKML LookML

sales_yoy.sql · 70 lines

inferred

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

SELECT
coalesce(cy.datekey,NULL) as datekey,
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoday,py.isoday) as isoday,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,

coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
coalesce(cy.categorygroup,py.categorygroup) as categorygroup,
coalesce(cy.category,py.category) as category,
coalesce(cy.subcategory ,py.subcategory ) as subcategory,
coalesce(cy.itemdescription,py.itemdescription)  as itemdescription,
cy.avgprice, cy.quantity, cy.grosssales,cy.netsales ,
py.quantity as quantity_ly, py.netsales as netsales_ly, py.grosssales as grosssales_py, py.datekey as datekey_py

FROM
(
SELECT F.* ,D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey
) cy

FULL OUTER JOIN

(SELECT  F.* , D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey

) py
on cy.isoweek = py.isoweek
and cy.isoday = py.isoday
and cy.isoyear = py.isoyear+1
and cy.salesoutletkey = py.salesoutletkey
and cy.sourcekey = py.sourcekey
and cy.itemdescription = py.itemdescription

where cy.isoyear > 2014 or py.isoyear>2013

# models/schema.yml
version: 2

models:
  - name: sales_yoy
    columns:
      - name: datekey
      - name: datekey_outlet
        tests:
          - unique
          - not_null
      - name: isoyear
      - name: isoweek
      - name: isoday
      - name: sales_outlet_key
      - name: categorygroup
      - name: category
      - name: subcategory
      - name: itemdescription
      - name: avgprice
      - name: quantity
      - name: grosssales
      - name: netsales
      - name: quantity_ly
      - name: netsales_ly
      - name: grosssales_py
      - name: datekey_py

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.

sales_yoy.sql · 39 lines

source-preserved validation: parses

SELECT
coalesce(cy.datekey,NULL) as datekey,
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoday,py.isoday) as isoday,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,

coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
coalesce(cy.categorygroup,py.categorygroup) as categorygroup,
coalesce(cy.category,py.category) as category,
coalesce(cy.subcategory ,py.subcategory ) as subcategory,
coalesce(cy.itemdescription,py.itemdescription)  as itemdescription,
cy.avgprice, cy.quantity, cy.grosssales,cy.netsales ,
py.quantity as quantity_ly, py.netsales as netsales_ly, py.grosssales as grosssales_py, py.datekey as datekey_py

FROM
(
SELECT F.* ,D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey
) cy

FULL OUTER JOIN

(SELECT  F.* , D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey

) py
on cy.isoweek = py.isoweek
and cy.isoday = py.isoday
and cy.isoyear = py.isoyear+1
and cy.salesoutletkey = py.salesoutletkey
and cy.sourcekey = py.sourcekey
and cy.itemdescription = py.itemdescription

where cy.isoyear > 2014 or py.isoyear>2013

sales_yoy.view.lkml · 199 lines

inferred

view: sales_yoy {
  derived_table: {
    sql: SELECT
coalesce(cy.datekey,NULL) as datekey,
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoday,py.isoday) as isoday,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,

coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
coalesce(cy.categorygroup,py.categorygroup) as categorygroup,
coalesce(cy.category,py.category) as category,
coalesce(cy.subcategory ,py.subcategory ) as subcategory,
coalesce(cy.itemdescription,py.itemdescription)  as itemdescription,
cy.avgprice, cy.quantity, cy.grosssales,cy.netsales ,
py.quantity as quantity_ly, py.netsales as netsales_ly, py.grosssales as grosssales_py, py.datekey as datekey_py

FROM
(
SELECT F.* ,D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey
) cy

FULL OUTER JOIN

(SELECT  F.* , D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey

) py
on cy.isoweek = py.isoweek
and cy.isoday = py.isoday
and cy.isoyear = py.isoyear+1
and cy.salesoutletkey = py.salesoutletkey
and cy.sourcekey = py.sourcekey
and cy.itemdescription = py.itemdescription

where cy.isoyear > 2014 or py.isoyear>2013 ;;
    persist_for: "6 hours"
  }

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

  dimension: datekey_outlet {
    primary_key: yes
    hidden: yes
    sql: CONCAT(${TABLE}.datekey, ${TABLE}.isoday,${TABLE}.isoweek,${TABLE}.isoyear, ${TABLE}.itemdescription, ${TABLE}.salesoutletkey) ;;
  }

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

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

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

  dimension: sales_outlet_key {
    type: number
    sql: ${TABLE}.salesoutletkey ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: total_quantity {
    type: sum
    sql: ${TABLE}.quantity ;;
  }

  measure: total_gross_sales {
    type: sum
    sql: ${TABLE}.grosssales ;;
  }

  measure: total_net_sales {
    type: sum
    sql: ${TABLE}.netsales ;;
  }

  measure: running_total_net_sales {
    type: running_total
    sql: ${total_net_sales} ;;
  }

  measure: running_total_net_sales_ly {
    type: running_total
    sql: ${total_net_sales_ly} ;;
  }

  measure: total_quantity_ly {
    type: sum
    sql: ${TABLE}.quantity_ly ;;
  }

  measure: total_net_sales_ly {
    type: sum
    sql: ${TABLE}.netsales_ly ;;
  }

  measure: total_gross_sales_ly {
    type: sum
    sql: ${TABLE}.grosssales_py ;;
  }

  measure: percent_gross_sales_yoy {
    type: number
    value_format: "#.00%"
    sql: ${total_gross_sales} / NULLIF(${total_gross_sales_ly},0) -1 ;;
  }

  measure: percent_net_sales_yoy {
    type: number
    value_format: "#.00%"
    sql: ${total_net_sales} / NULLIF(${total_net_sales_ly},0) -1 ;;
  }

  measure: gross_sales_yoy {
    type: number
    sql: ${total_gross_sales} - ${total_gross_sales_ly} ;;
  }

  measure: net_sales_yoy {
    type: number
    sql: ${total_net_sales} - ${total_net_sales_ly} ;;
  }
}

sales_yoy.view.lookml · 137 lines

source-preserved

- view: sales_yoy
  derived_table:
    sql: SELECT
coalesce(cy.datekey,NULL) as datekey,
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoday,py.isoday) as isoday,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,

coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
coalesce(cy.categorygroup,py.categorygroup) as categorygroup,
coalesce(cy.category,py.category) as category,
coalesce(cy.subcategory ,py.subcategory ) as subcategory,
coalesce(cy.itemdescription,py.itemdescription)  as itemdescription,
cy.avgprice, cy.quantity, cy.grosssales,cy.netsales ,
py.quantity as quantity_ly, py.netsales as netsales_ly, py.grosssales as grosssales_py, py.datekey as datekey_py

FROM
(
SELECT F.* ,D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey
) cy

FULL OUTER JOIN

(SELECT  F.* , D.isoweek, D.isoday, d.isoyear
from FactAggItemSalesByDay F
left join dimdate D
on  F.datekey = D.datekey

) py
on cy.isoweek = py.isoweek
and cy.isoday = py.isoday
and cy.isoyear = py.isoyear+1
and cy.salesoutletkey = py.salesoutletkey
and cy.sourcekey = py.sourcekey
and cy.itemdescription = py.itemdescription

where cy.isoyear > 2014 or py.isoyear>2013
    persist_for: 6 hours
  fields:
  - dimension: datekey
    type: int
    sql: ${TABLE}.datekey
  - dimension: datekey_outlet
    primary_key: true
    hidden: true
    sql: CONCAT(${TABLE}.datekey, ${TABLE}.isoday,${TABLE}.isoweek,${TABLE}.isoyear, ${TABLE}.itemdescription, ${TABLE}.salesoutletkey)
  - dimension: isoyear
    type: int
    sql: ${TABLE}.isoyear
  - dimension: isoweek
    type: number
    sql: ${TABLE}.isoweek
  - dimension: isoday
    type: number
    sql: ${TABLE}.isoday
  - dimension: sales_outlet_key
    type: number
    sql: ${TABLE}.salesoutletkey
  - dimension: categorygroup
    sql: ${TABLE}.categorygroup
  - dimension: category
    sql: ${TABLE}.category
  - dimension: subcategory
    sql: ${TABLE}.subcategory
  - dimension: itemdescription
    sql: ${TABLE}.itemdescription
  - dimension: avgprice
    type: number
    hidden: true
    sql: ${TABLE}.avgprice
  - dimension: quantity
    type: number
    sql: ${TABLE}.quantity
  - dimension: grosssales
    type: number
    sql: ${TABLE}.grosssales
  - dimension: netsales
    type: number
    sql: ${TABLE}.netsales
  - dimension: quantity_ly
    type: number
    sql: ${TABLE}.quantity_ly
  - dimension: netsales_ly
    type: number
    sql: ${TABLE}.netsales_ly
  - dimension: grosssales_py
    type: number
    sql: ${TABLE}.grosssales_py
  - dimension: datekey_py
    type: number
    sql: ${TABLE}.datekey_py
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_quantity
    type: sum
    sql: ${TABLE}.quantity
  - measure: total_gross_sales
    type: sum
    sql: ${TABLE}.grosssales
  - measure: total_net_sales
    type: sum
    sql: ${TABLE}.netsales
  - measure: running_total_net_sales
    type: running_total
    sql: ${total_net_sales}
  - measure: running_total_net_sales_ly
    type: running_total
    sql: ${total_net_sales_ly}
  - measure: total_quantity_ly
    type: sum
    sql: ${TABLE}.quantity_ly
  - measure: total_net_sales_ly
    type: sum
    sql: ${TABLE}.netsales_ly
  - measure: total_gross_sales_ly
    type: sum
    sql: ${TABLE}.grosssales_py
  - measure: percent_gross_sales_yoy
    type: number
    value_format: '#.00%'
    sql: ${total_gross_sales} / NULLIF(${total_gross_sales_ly},0) -1
  - measure: percent_net_sales_yoy
    type: number
    value_format: '#.00%'
    sql: ${total_net_sales} / NULLIF(${total_net_sales_ly},0) -1
  - measure: gross_sales_yoy
    type: number
    sql: ${total_gross_sales} - ${total_gross_sales_ly}
  - measure: net_sales_yoy
    type: number
    sql: ${total_net_sales} - ${total_net_sales_ly}

sales_yoy.sql
sales_yoy.sql
sales_yoy.view.lkml
sales_yoy.view.lookml

Sales Yoy

Blocks

Assumptions

Contents