dbt Ecommerce beverage

Is Orders Simple

Is Orders Simple model for ecommerce beverage.

inferred

Blocks

dbt SQL LKML LookML

is_orders_simple.sql · 42 lines

inferred

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

SELECT
  j.Id 'id'
  ,j.ContactId 'contact_id'
  ,j.DateCreated 'date_created'
  ,j.JobTitle 'job_title'
  ,MAX(i.Id) 'invoice_id'
  ,MAX(i.InvoiceTotal) 'invoice_total'
  ,SUM(IF(oi.ItemType IN (4,9), oi.Qty * oi.PPU, 0)) 'subtotal'
  ,SUM(IF(oi.ItemType IN (7,10,11,12,13), oi.Qty * oi.PPU, 0)) 'discount'
  ,SUM(IF(oi.ItemType = 2, oi.Qty * oi.PPU, 0)) 'tax'
  ,SUM(IF(oi.ItemType IN (1,14), oi.Qty * oi.PPU, 0)) 'shipping'
  ,COUNT(IF(oi.ItemType=9, 1, NULL))>0 'has_subscription_item'
  ,COUNT(IF(p.Sku=SKU_Generic, 1, NULL))>0 'has_2cans'
FROM Company_Jobs j
LEFT JOIN Company_Invoices i ON i.JobId=j.Id
LEFT JOIN Company_OrderItems oi ON oi.OrderId=j.Id
LEFT JOIN Company_Products p ON p.Id=oi.ProductId
GROUP BY j.Id

# models/schema.yml
version: 2

models:
  - name: is_orders_simple
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: contact_id
      - name: job_title
      - name: invoice_id
      - name: invoice_total
      - name: subtotal
      - name: discount
      - name: tax
      - name: shipping
      - name: has_subscription_item
      - name: subscription_order

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.

is_orders_simple.sql · 18 lines

source-preserved validation: not checked

SELECT
  j.Id 'id'
  ,j.ContactId 'contact_id'
  ,j.DateCreated 'date_created'
  ,j.JobTitle 'job_title'
  ,MAX(i.Id) 'invoice_id'
  ,MAX(i.InvoiceTotal) 'invoice_total'
  ,SUM(IF(oi.ItemType IN (4,9), oi.Qty * oi.PPU, 0)) 'subtotal'
  ,SUM(IF(oi.ItemType IN (7,10,11,12,13), oi.Qty * oi.PPU, 0)) 'discount'
  ,SUM(IF(oi.ItemType = 2, oi.Qty * oi.PPU, 0)) 'tax'
  ,SUM(IF(oi.ItemType IN (1,14), oi.Qty * oi.PPU, 0)) 'shipping'
  ,COUNT(IF(oi.ItemType=9, 1, NULL))>0 'has_subscription_item'
  ,COUNT(IF(p.Sku=SKU_Generic, 1, NULL))>0 'has_2cans'
FROM Company_Jobs j
LEFT JOIN Company_Invoices i ON i.JobId=j.Id
LEFT JOIN Company_OrderItems oi ON oi.OrderId=j.Id
LEFT JOIN Company_Products p ON p.Id=oi.ProductId
GROUP BY j.Id

is_orders_simple.view.lkml · 132 lines

inferred

view: is_orders_simple {
  derived_table: {
    sql: SELECT
  j.Id 'id'
  ,j.ContactId 'contact_id'
  ,j.DateCreated 'date_created'
  ,j.JobTitle 'job_title'
  ,MAX(i.Id) 'invoice_id'
  ,MAX(i.InvoiceTotal) 'invoice_total'
  ,SUM(IF(oi.ItemType IN (4,9), oi.Qty * oi.PPU, 0)) 'subtotal'
  ,SUM(IF(oi.ItemType IN (7,10,11,12,13), oi.Qty * oi.PPU, 0)) 'discount'
  ,SUM(IF(oi.ItemType = 2, oi.Qty * oi.PPU, 0)) 'tax'
  ,SUM(IF(oi.ItemType IN (1,14), oi.Qty * oi.PPU, 0)) 'shipping'
  ,COUNT(IF(oi.ItemType=9, 1, NULL))>0 'has_subscription_item'
  ,COUNT(IF(p.Sku=SKU_Generic, 1, NULL))>0 'has_2cans'
FROM Company_Jobs j
LEFT JOIN Company_Invoices i ON i.JobId=j.Id
LEFT JOIN Company_OrderItems oi ON oi.OrderId=j.Id
LEFT JOIN Company_Products p ON p.Id=oi.ProductId
GROUP BY j.Id ;;
  }

  dimension: id {
    primary_key: yes
  }

  dimension: contact_id {
    type: int
  }

  dimension: job_title {
  }

  dimension: invoice_id {
  }

  dimension: invoice_total {
    type: number
    value_format: "$#,##0"
  }

  dimension: subtotal {
    type: number
    value_format: "$#,##0"
  }

  dimension: discount {
    type: number
    value_format: "$#,##0"
  }

  dimension: tax {
    type: number
    value_format: "$#,##0"
  }

  dimension: shipping {
    type: number
    value_format: "$#,##0"
  }

  dimension: has_subscription_item {
    hidden: yes
  }

  dimension: subscription_order {
    type: yesno
    sql: ${has_subscription_item} ;;
  }

  dimension_group: date_created {
    type: time
    timeframes: [time, date, week, month, year]
  }

  measure: count {
    type: count
    drill_fields: [${id}]
  }

  measure: subscription_orders_count {
    type: count_distinct
    sql: IF(${has_subscription_item}, ${id}, NULL) ;;
  }

  measure: total_order_amount {
    type: sum
    value_format: "$#,##0"
    sql: COALESCE(${invoice_total},0) ;;
  }

  measure: total_discount {
    type: sum
    value_format: "$#,##0"
    sql: ${discount} ;;
  }

  measure: total_tax {
    type: sum
    value_format: "$#,##0"
    sql: COALESCE(${tax}, 0) ;;
  }

  measure: total_shipping {
    type: sum
    value_format: "$#,##0"
    sql: COALESCE(${shipping}, 0) ;;
  }

  measure: total_dollars_products {
    type: sum
    value_format: "$#,##0"
    sql: COALESCE(${subtotal}, 0) ;;
  }

  measure: number_distinct_customers_ordering {
    type: count_distinct
    sql: ${contact_id} ;;
  }

  measure: volume_per_account {
    type: number
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${total_order_amount}/${number_distinct_customers_ordering} ;;
  }

  measure: non_2cans_order {
    type: number
    sql: ${count} - ${2cans_orders_count} ;;
  }
}

is_orders_simple.view.lookml · 94 lines

source-preserved

- view: is_orders_simple
  derived_table:
    sql: SELECT
  j.Id 'id'
  ,j.ContactId 'contact_id'
  ,j.DateCreated 'date_created'
  ,j.JobTitle 'job_title'
  ,MAX(i.Id) 'invoice_id'
  ,MAX(i.InvoiceTotal) 'invoice_total'
  ,SUM(IF(oi.ItemType IN (4,9), oi.Qty * oi.PPU, 0)) 'subtotal'
  ,SUM(IF(oi.ItemType IN (7,10,11,12,13), oi.Qty * oi.PPU, 0)) 'discount'
  ,SUM(IF(oi.ItemType = 2, oi.Qty * oi.PPU, 0)) 'tax'
  ,SUM(IF(oi.ItemType IN (1,14), oi.Qty * oi.PPU, 0)) 'shipping'
  ,COUNT(IF(oi.ItemType=9, 1, NULL))>0 'has_subscription_item'
  ,COUNT(IF(p.Sku=SKU_Generic, 1, NULL))>0 'has_2cans'
FROM Company_Jobs j
LEFT JOIN Company_Invoices i ON i.JobId=j.Id
LEFT JOIN Company_OrderItems oi ON oi.OrderId=j.Id
LEFT JOIN Company_Products p ON p.Id=oi.ProductId
GROUP BY j.Id
  fields:
  - dimension: id
    primary_key: true
  - dimension: contact_id
    type: int
  - dimension: job_title
  - dimension: invoice_id
  - dimension: invoice_total
    type: number
    value_format: '$#,##0'
  - dimension: subtotal
    type: number
    value_format: '$#,##0'
  - dimension: discount
    type: number
    value_format: '$#,##0'
  - dimension: tax
    type: number
    value_format: '$#,##0'
  - dimension: shipping
    type: number
    value_format: '$#,##0'
  - dimension: has_subscription_item
    hidden: true
  - dimension: subscription_order
    type: yesno
    sql: ${has_subscription_item}
  - dimension_group: date_created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
  - measure: count
    type: count
    drill_fields:
    - ${id}
  - measure: subscription_orders_count
    type: count_distinct
    sql: IF(${has_subscription_item}, ${id}, NULL)
  - measure: total_order_amount
    type: sum
    value_format: '$#,##0'
    sql: COALESCE(${invoice_total},0)
  - measure: total_discount
    type: sum
    value_format: '$#,##0'
    sql: ${discount}
  - measure: total_tax
    type: sum
    value_format: '$#,##0'
    sql: COALESCE(${tax}, 0)
  - measure: total_shipping
    type: sum
    value_format: '$#,##0'
    sql: COALESCE(${shipping}, 0)
  - measure: total_dollars_products
    type: sum
    value_format: '$#,##0'
    sql: COALESCE(${subtotal}, 0)
  - measure: number_distinct_customers_ordering
    type: count_distinct
    sql: ${contact_id}
  - measure: volume_per_account
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${total_order_amount}/${number_distinct_customers_ordering}
  - measure: non_2cans_order
    type: number
    sql: ${count} - ${2cans_orders_count}

is_orders_simple.sql
is_orders_simple.sql
is_orders_simple.view.lkml
is_orders_simple.view.lookml

Is Orders Simple

Blocks

Assumptions

Contents