SQL Delivery alcohol

Promoted Orders

Promoted Orders model for delivery alcohol.

source-preserved

Blocks

dbt SQL LKML LookML

promoted_orders.sql · 41 lines

inferred

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

SELECT
  DISTINCT so.order_id,
  sg.group,
  o.created_at,
  pt.id AS type_id,
  pt.description,
  pc.redemption_code,
  so.promo_code_id,
  IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount,
  o.order_total
      FROM orders AS so
      LEFT JOIN stores AS s ON so.store_id = s.id
      LEFT JOIN orders AS o ON o.id = so.order_id
      LEFT JOIN market_xrefs AS xr ON so.store_id = xr.store_id
      LEFT JOIN groups AS sg ON xr.market_id = sg.id
      LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
      LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
      LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
      WHERE so.promo_code_id IS NOT NULL
      AND so.status = 6
      AND so.store_id <> 2
      GROUP BY so.order_id

# models/schema.yml
version: 2

models:
  - name: promoted_orders
    columns:
      - name: order_id
      - name: group
      - name: type_id
      - name: description
      - name: redemption_code
      - name: promo_code_id
      - name: promo_code_discount
      - name: order_total
      - name: delivery_fee

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.

promoted_orders.sql · 22 lines

source-preserved validation: not checked

SELECT
  DISTINCT so.order_id,
  sg.group,
  o.created_at,
  pt.id AS type_id,
  pt.description,
  pc.redemption_code,
  so.promo_code_id,
  IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount,
  o.order_total
      FROM orders AS so
      LEFT JOIN stores AS s ON so.store_id = s.id
      LEFT JOIN orders AS o ON o.id = so.order_id
      LEFT JOIN market_xrefs AS xr ON so.store_id = xr.store_id
      LEFT JOIN groups AS sg ON xr.market_id = sg.id
      LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
      LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
      LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
      WHERE so.promo_code_id IS NOT NULL
      AND so.status = 6
      AND so.store_id <> 2
      GROUP BY so.order_id

promoted_orders.view.lkml · 92 lines

inferred

view: promoted_orders {
  derived_table: {
    sql: SELECT
  DISTINCT so.order_id,
  sg.group,
  o.created_at,
  pt.id AS type_id,
  pt.description,
  pc.redemption_code,
  so.promo_code_id,
  IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount,
  o.order_total
      FROM orders AS so
      LEFT JOIN stores AS s ON so.store_id = s.id
      LEFT JOIN orders AS o ON o.id = so.order_id
      LEFT JOIN market_xrefs AS xr ON so.store_id = xr.store_id
      LEFT JOIN groups AS sg ON xr.market_id = sg.id
      LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
      LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
      LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
      WHERE so.promo_code_id IS NOT NULL
      AND so.status = 6
      AND so.store_id <> 2
      GROUP BY so.order_id ;;
  }

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

  dimension: group {
    label: "Market"
    sql: ${TABLE}.group ;;
  }

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

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

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

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

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

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

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

  dimension_group: created_at {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_at ;;
  }

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

  measure: total_promo_code_discount {
    type: sum
    drill_fields: [detail*]
    sql: ${promo_code_discount} ;;
  }

  measure: TPV {
    type: sum
    drill_fields: [detail*]
    sql: (${order_total} + ${promo_code_discount}) ;;
  }
}

promoted_orders.view.lookml · 72 lines

source-preserved

- view: promoted_orders
  derived_table:
    sql: SELECT
  DISTINCT so.order_id,
  sg.group,
  o.created_at,
  pt.id AS type_id,
  pt.description,
  pc.redemption_code,
  so.promo_code_id,
  IFNULL(SUM({% parameter state_6 %}SE WHEN promo_code_type_id = 3 OR (promo_code_type_id = 4 AND s.state != '{% parameter state_3 %}') THEN s.fee ELSE so.promo_code_discount END),0) as promo_code_discount,
  o.order_total
      FROM orders AS so
      LEFT JOIN stores AS s ON so.store_id = s.id
      LEFT JOIN orders AS o ON o.id = so.order_id
      LEFT JOIN market_xrefs AS xr ON so.store_id = xr.store_id
      LEFT JOIN groups AS sg ON xr.market_id = sg.id
      LEFT JOIN promo_code_redemptions AS pr ON so.order_id = pr.order_id
      LEFT JOIN promo_codes AS pc ON pr.promo_code_id = pc.id
      LEFT JOIN promo_code_types AS pt ON pc.promo_code_type_id = pt.id
      WHERE so.promo_code_id IS NOT NULL
      AND so.status = 6
      AND so.store_id <> 2
      GROUP BY so.order_id
  fields:
  - dimension: order_id
    type: int
    sql: ${TABLE}.order_id
  - dimension: group
    label: Market
    sql: ${TABLE}.group
  - dimension: type_id
    type: number
    sql: ${TABLE}.type_id
  - dimension: description
    sql: ${TABLE}.description
  - dimension: redemption_code
    sql: ${TABLE}.redemption_code
  - dimension: promo_code_id
    type: int
    sql: ${TABLE}.promo_code_id
  - dimension: promo_code_discount
    type: number
    sql: ${TABLE}.promo_code_discount
  - dimension: order_total
    type: number
    sql: ${TABLE}.order_total
  - dimension: delivery_fee
    type: number
    sql: ${TABLE}.delivery_fee
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_promo_code_discount
    type: sum
    drill_fields:
    - detail*
    sql: ${promo_code_discount}
  - measure: TPV
    type: sum
    drill_fields:
    - detail*
    sql: (${order_total} + ${promo_code_discount})

promoted_orders.sql
promoted_orders.sql
promoted_orders.view.lkml
promoted_orders.view.lookml

Promoted Orders

Blocks

Assumptions

Contents