SQL Delivery app

Admin Query

Admin Query model for delivery app.

source-preserved

Blocks

dbt SQL LKML LookML

admin_query.sql · 54 lines

inferred

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

select
        date(dw.ship_date) as ship
,       f.name as facility
,       f.id as facility_id
,       pc.shorealtime_code as product_configuration
,       pc.id as product_configuration_id
,       pcst.sorealtime_order||' - '||pcst.meal_type as meal_type
,       r.main_title
,       dw.id as delivery_window_id
,       p.name as plan_name
,       sm.delivery_type as carrier
,       sm.transit_days
,       sum(case when o.serving_size is not null then serving_size / 2 else 1 end) as boxes
from orders o
join analytics_delivery_windows dw on o.delivery_window_id = dw.id
join analytics_deliveries d on dw.delivery_id = d.id
join analytics_windows w on w.id = dw.window_id
join analytics_shipping_methods sm on w.shipping_method_id = sm.id
join analytics_facilities f on sm.facility_id = f.id
join analytics_product_configurations pc on o.product_configuration_id = pc.id
join analytics_product_configuration_slots pcs on pcs.product_configuration_id=o.product_configuration_id
join deliveries_recipes dr on d.id = dr.delivery_id and dr.product_configuration_slot_type_id=pcs.product_configuration_slot_type_id
join recipes r on dr.recipe_id = r.id
join analytics_product_configuration_slot_types pcst on pcst.id = dr.product_configuration_slot_type_id
join analytics_plan_options po on o.plan_option_id = po.id
join analytics_plans p on po.plan_id = p.id
join analytics_plan_types pt on pt.id=p.plan_type_id
where (o.status in ('Scheduled', 'Paid') or o.label_generated_at is not null)
  and pt.name='FOOD'
  and (o.is_canceled=FALSE or o.is_canceled is null)
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11

# models/schema.yml
version: 2

models:
  - name: admin_query
    columns:
      - name: ship
      - name: facility
      - name: facility_id
      - name: product_configuration_id
      - name: meal_configuration_id
      - name: delivery_window_id
      - name: plan
      - name: product_configuration
      - name: meal_configuration
      - name: meal_type
      - name: main_title
      - name: carrier
      - name: transit_days

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.

admin_query.sql · 31 lines

source-preserved validation: parses

select
        date(dw.ship_date) as ship
,       f.name as facility
,       f.id as facility_id
,       pc.shorealtime_code as product_configuration
,       pc.id as product_configuration_id
,       pcst.sorealtime_order||' - '||pcst.meal_type as meal_type
,       r.main_title
,       dw.id as delivery_window_id
,       p.name as plan_name
,       sm.delivery_type as carrier
,       sm.transit_days
,       sum(case when o.serving_size is not null then serving_size / 2 else 1 end) as boxes
from orders o
join analytics_delivery_windows dw on o.delivery_window_id = dw.id
join analytics_deliveries d on dw.delivery_id = d.id
join analytics_windows w on w.id = dw.window_id
join analytics_shipping_methods sm on w.shipping_method_id = sm.id
join analytics_facilities f on sm.facility_id = f.id
join analytics_product_configurations pc on o.product_configuration_id = pc.id
join analytics_product_configuration_slots pcs on pcs.product_configuration_id=o.product_configuration_id
join deliveries_recipes dr on d.id = dr.delivery_id and dr.product_configuration_slot_type_id=pcs.product_configuration_slot_type_id
join recipes r on dr.recipe_id = r.id
join analytics_product_configuration_slot_types pcst on pcst.id = dr.product_configuration_slot_type_id
join analytics_plan_options po on o.plan_option_id = po.id
join analytics_plans p on po.plan_id = p.id
join analytics_plan_types pt on pt.id=p.plan_type_id
where (o.status in ('Scheduled', 'Paid') or o.label_generated_at is not null)
  and pt.name='FOOD'
  and (o.is_canceled=FALSE or o.is_canceled is null)
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11

admin_query.view.lkml · 123 lines

inferred

view: admin_query {
  derived_table: {
    sql: select
        date(dw.ship_date) as ship
,       f.name as facility
,       f.id as facility_id
,       pc.shorealtime_code as product_configuration
,       pc.id as product_configuration_id
,       pcst.sorealtime_order||' - '||pcst.meal_type as meal_type
,       r.main_title
,       dw.id as delivery_window_id
,       p.name as plan_name
,       sm.delivery_type as carrier
,       sm.transit_days
,       sum(case when o.serving_size is not null then serving_size / 2 else 1 end) as boxes
from orders o
join analytics_delivery_windows dw on o.delivery_window_id = dw.id
join analytics_deliveries d on dw.delivery_id = d.id
join analytics_windows w on w.id = dw.window_id
join analytics_shipping_methods sm on w.shipping_method_id = sm.id
join analytics_facilities f on sm.facility_id = f.id
join analytics_product_configurations pc on o.product_configuration_id = pc.id
join analytics_product_configuration_slots pcs on pcs.product_configuration_id=o.product_configuration_id
join deliveries_recipes dr on d.id = dr.delivery_id and dr.product_configuration_slot_type_id=pcs.product_configuration_slot_type_id
join recipes r on dr.recipe_id = r.id
join analytics_product_configuration_slot_types pcst on pcst.id = dr.product_configuration_slot_type_id
join analytics_plan_options po on o.plan_option_id = po.id
join analytics_plans p on po.plan_id = p.id
join analytics_plan_types pt on pt.id=p.plan_type_id
where (o.status in ('Scheduled', 'Paid') or o.label_generated_at is not null)
  and pt.name='FOOD'
  and (o.is_canceled=FALSE or o.is_canceled is null)
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ;;
    persist_for: "1 hour"
  }

  dimension: ship {
    label: "SHIP"
    type: time
    sql: ${TABLE}.ship ;;
  }

  dimension: facility {
    label: "FACILITY"
    type: string
    sql: ${TABLE}.facility ;;
  }

  dimension: facility_id {
    label: "FACILITY ID"
    type: int
    sql: ${TABLE}.facility_id ;;
  }

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

  dimension: meal_configuration_id {
    type: int
    hidden: yes
    sql: ${TABLE}.product_configuration_id ;;
  }

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

  dimension: plan {
    label: "PLAN"
    sql: ${TABLE}.plan_name ;;
  }

  dimension: product_configuration {
    label: "MEAL Config"
    sql: ${TABLE}.product_configuration ;;
  }

  dimension: meal_configuration {
    label: "MEAL Config"
    hidden: yes
    sql: ${TABLE}.product_configuration ;;
  }

  dimension: meal_type {
    label: "MEAL Type"
    sql: ${TABLE}.meal_type ;;
  }

  dimension: main_title {
    label: "RECIPE Title"
  }

  dimension: carrier {
    label: "CARRIER"
    sql: ${TABLE}.carrier ;;
  }

  dimension: transit_days {
    label: "TRANSIT Days"
    sql: ${TABLE}.transit_days ;;
  }

  measure: boxes {
    label: "COUNT"
    type: sum
    sql: ${TABLE}.boxes ;;
  }

  measure: delta {
    label: "DELTA"
    type: int
    sql: ${boxes} - ${cutoffs} ;;
  }

  measure: dropoff {
    label: "DROPOFF"
    type: number
    sql: (${boxes}/${cutoffs} - 1) * 100 ;;
  }
}

admin_query.view.lookml · 90 lines

source-preserved

- view: admin_query
  derived_table:
    sql: select
        date(dw.ship_date) as ship
,       f.name as facility
,       f.id as facility_id
,       pc.shorealtime_code as product_configuration
,       pc.id as product_configuration_id
,       pcst.sorealtime_order||' - '||pcst.meal_type as meal_type
,       r.main_title
,       dw.id as delivery_window_id
,       p.name as plan_name
,       sm.delivery_type as carrier
,       sm.transit_days
,       sum(case when o.serving_size is not null then serving_size / 2 else 1 end) as boxes
from orders o
join analytics_delivery_windows dw on o.delivery_window_id = dw.id
join analytics_deliveries d on dw.delivery_id = d.id
join analytics_windows w on w.id = dw.window_id
join analytics_shipping_methods sm on w.shipping_method_id = sm.id
join analytics_facilities f on sm.facility_id = f.id
join analytics_product_configurations pc on o.product_configuration_id = pc.id
join analytics_product_configuration_slots pcs on pcs.product_configuration_id=o.product_configuration_id
join deliveries_recipes dr on d.id = dr.delivery_id and dr.product_configuration_slot_type_id=pcs.product_configuration_slot_type_id
join recipes r on dr.recipe_id = r.id
join analytics_product_configuration_slot_types pcst on pcst.id = dr.product_configuration_slot_type_id
join analytics_plan_options po on o.plan_option_id = po.id
join analytics_plans p on po.plan_id = p.id
join analytics_plan_types pt on pt.id=p.plan_type_id
where (o.status in ('Scheduled', 'Paid') or o.label_generated_at is not null)
  and pt.name='FOOD'
  and (o.is_canceled=FALSE or o.is_canceled is null)
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
    persist_for: 1 hour
  fields:
  - dimension: ship
    type: time
    label: SHIP
    sql: ${TABLE}.ship
  - dimension: facility
    type: string
    label: FACILITY
    sql: ${TABLE}.facility
  - dimension: facility_id
    type: int
    label: FACILITY ID
    sql: ${TABLE}.facility_id
  - dimension: product_configuration_id
    type: int
    sql: ${TABLE}.product_configuration_id
  - dimension: meal_configuration_id
    type: int
    hidden: true
    sql: ${TABLE}.product_configuration_id
  - dimension: delivery_window_id
    type: int
    sql: ${TABLE}.delivery_window_id
  - dimension: plan
    label: PLAN
    sql: ${TABLE}.plan_name
  - dimension: product_configuration
    label: MEAL Config
    sql: ${TABLE}.product_configuration
  - dimension: meal_configuration
    hidden: true
    label: MEAL Config
    sql: ${TABLE}.product_configuration
  - dimension: meal_type
    label: MEAL Type
    sql: ${TABLE}.meal_type
  - dimension: main_title
    label: RECIPE Title
  - dimension: carrier
    label: CARRIER
    sql: ${TABLE}.carrier
  - dimension: transit_days
    label: TRANSIT Days
    sql: ${TABLE}.transit_days
  - measure: boxes
    type: sum
    label: COUNT
    sql: ${TABLE}.boxes
  - measure: delta
    type: int
    label: DELTA
    sql: ${boxes} - ${cutoffs}
  - measure: dropoff
    type: number
    label: DROPOFF
    sql: (${boxes}/${cutoffs} - 1) * 100

admin_query.sql
admin_query.sql
admin_query.view.lkml
admin_query.view.lookml

Admin Query

Blocks

Assumptions

Contents