dbt Retail gastronomy

Sales Weekly Yoy

Sales Weekly Yoy model for retail gastronomy.

inferred

Blocks

dbt SQL LKML LookML

sales_weekly_yoy.sql · 65 lines

inferred

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

SELECT
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,
coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
SUM(cy.quantity) as quantity,
SUM(cy.grosssales) as grosssales,
SUM(cy.netsales) as netsales ,
SUM(py.quantity) as quantity_ly,
SUM(py.netsales) as netsales_ly,
SUM(py.grosssales) as grosssales_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

Group by
coalesce(cy.isoweek,py.isoweek) ,
coalesce(cy.isoyear,py.isoyear+1) ,
coalesce(cy.salesoutletkey,py.salesoutletkey) ,
coalesce(cy.sourcekey,py.sourcekey)

# models/schema.yml
version: 2

models:
  - name: sales_weekly_yoy
    columns:
      - name: datekey_outlet
        tests:
          - unique
          - not_null
      - name: isoyear
      - name: isoweek
      - name: sales_outlet_key
      - name: quantity
      - name: grosssales
      - name: netsales
      - name: quantity_ly
      - name: netsales_ly
      - name: grosssales_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_weekly_yoy.sql · 42 lines

source-preserved validation: parses

SELECT
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,
coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
SUM(cy.quantity) as quantity,
SUM(cy.grosssales) as grosssales,
SUM(cy.netsales) as netsales ,
SUM(py.quantity) as quantity_ly,
SUM(py.netsales) as netsales_ly,
SUM(py.grosssales) as grosssales_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

Group by
coalesce(cy.isoweek,py.isoweek) ,
coalesce(cy.isoyear,py.isoyear+1) ,
coalesce(cy.salesoutletkey,py.salesoutletkey) ,
coalesce(cy.sourcekey,py.sourcekey)

sales_weekly_yoy.view.lkml · 177 lines

inferred

view: sales_weekly_yoy {
  derived_table: {
    sql: SELECT
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,
coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
SUM(cy.quantity) as quantity,
SUM(cy.grosssales) as grosssales,
SUM(cy.netsales) as netsales ,
SUM(py.quantity) as quantity_ly,
SUM(py.netsales) as netsales_ly,
SUM(py.grosssales) as grosssales_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

Group by
coalesce(cy.isoweek,py.isoweek) ,
coalesce(cy.isoyear,py.isoyear+1) ,
coalesce(cy.salesoutletkey,py.salesoutletkey) ,
coalesce(cy.sourcekey,py.sourcekey) ;;
    persist_for: "6 hours"
  }

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

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

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

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

  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 ;;
  }

  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} ;;
  }

  measure: percent_of_total_net_sales {
    type: percent_of_total
    value_format: "#.00%"
    sql: ${total_net_sales} ;;
  }

  measure: percent_of_total_net_sales_ly {
    type: percent_of_total
    value_format: "#.00%"
    sql: ${total_net_sales_ly} ;;
  }
}

sales_weekly_yoy.view.lookml · 127 lines

source-preserved

- view: sales_weekly_yoy
  derived_table:
    sql: SELECT
coalesce(cy.isoweek,py.isoweek) as isoweek,
coalesce(cy.isoyear,py.isoyear+1) as isoyear,
coalesce(cy.salesoutletkey,py.salesoutletkey) as salesoutletkey,
coalesce(cy.sourcekey,py.sourcekey) as sourcekey,
SUM(cy.quantity) as quantity,
SUM(cy.grosssales) as grosssales,
SUM(cy.netsales) as netsales ,
SUM(py.quantity) as quantity_ly,
SUM(py.netsales) as netsales_ly,
SUM(py.grosssales) as grosssales_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

Group by
coalesce(cy.isoweek,py.isoweek) ,
coalesce(cy.isoyear,py.isoyear+1) ,
coalesce(cy.salesoutletkey,py.salesoutletkey) ,
coalesce(cy.sourcekey,py.sourcekey)
    persist_for: 6 hours
  fields:
  - dimension: datekey_outlet
    primary_key: true
    hidden: true
    sql: CONCAT(${TABLE}.isoweek,${TABLE}.isoyear,${TABLE}.salesoutletkey,${TABLE}.sourcekey)
  - dimension: isoyear
    type: int
    sql: ${TABLE}.isoyear
  - dimension: isoweek
    type: number
    sql: ${TABLE}.isoweek
  - dimension: sales_outlet_key
    type: number
    sql: ${TABLE}.salesoutletkey
  - 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
  - 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}
  - measure: percent_of_total_net_sales
    type: percent_of_total
    value_format: '#.00%'
    sql: ${total_net_sales}
  - measure: percent_of_total_net_sales_ly
    type: percent_of_total
    value_format: '#.00%'
    sql: ${total_net_sales_ly}

sales_weekly_yoy.sql
sales_weekly_yoy.sql
sales_weekly_yoy.view.lkml
sales_weekly_yoy.view.lookml

Sales Weekly Yoy

Blocks

Assumptions

Contents