Delivery Distance

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

SELECT
so.id as store_order_id
, so.status
, so.store_id
, s.internal_name
, so.order_id
, s.latitude
, s.longitude
, o.delivery_latitude
, o.delivery_longitude
,  degrees(acos(sin(radians(s.latitude)) * sin(radians(o.delivery_latitude)) + cos(radians(s.latitude)) * cos(radians(o.delivery_latitude)) * cos(radians(s.longitude - o.delivery_longitude)))) * 60 * 1.1515 as miles
FROM orders o
JOIN orders so ON o.id = so.order_id
JOIN stores s ON s.id = so.store_id
ORDER BY so.id

# models/schema.yml
version: 2

models:
  - name: delivery_distance
    columns:
      - name: store_order_id
        tests:
          - unique
          - not_null
      - name: store_id
      - name: internal_name
      - name: order_id
      - name: latitude
      - name: longitude
      - name: delivery_latitude
      - name: delivery_longitude
      - name: status
      - name: miles

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.

SELECT
so.id as store_order_id
, so.status
, so.store_id
, s.internal_name
, so.order_id
, s.latitude
, s.longitude
, o.delivery_latitude
, o.delivery_longitude
,  degrees(acos(sin(radians(s.latitude)) * sin(radians(o.delivery_latitude)) + cos(radians(s.latitude)) * cos(radians(o.delivery_latitude)) * cos(radians(s.longitude - o.delivery_longitude)))) * 60 * 1.1515 as miles
FROM orders o
JOIN orders so ON o.id = so.order_id
JOIN stores s ON s.id = so.store_id
ORDER BY so.id

view: delivery_distance {
  derived_table: {
    sql: SELECT
so.id as store_order_id
, so.status
, so.store_id
, s.internal_name
, so.order_id
, s.latitude
, s.longitude
, o.delivery_latitude
, o.delivery_longitude
,  degrees(acos(sin(radians(s.latitude)) * sin(radians(o.delivery_latitude)) + cos(radians(s.latitude)) * cos(radians(o.delivery_latitude)) * cos(radians(s.longitude - o.delivery_longitude)))) * 60 * 1.1515 as miles
FROM orders o
JOIN orders so ON o.id = so.order_id
JOIN stores s ON s.id = so.store_id
ORDER BY so.id ;;
  }

  dimension: store_order_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.store_order_id ;;
  }

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

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

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

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

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

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

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

  dimension: status {
    sql: {% parameter state_6 %}SE
  WHEN ${TABLE}.status = 6 THEN {% parameter order_status_completed %}
  WHEN ${TABLE}.status = 7 THEN 'Voided'
  WHEN ${TABLE}.status = 49 THEN 'Placed Gift'
  WHEN ${TABLE}.status = 50 THEN 'Scheduled Not Delivered'
ELSE 'NA'
END ;;
  }

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

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

  measure: total_distance_traveled {
    label: "Total Linear Distance Traveled"
    type: sum
    sql: ${miles} * 2 ;;
  }

  measure: avg_distance_traveled {
    label: "Avg Linear Distance Traveled Per Order"
    type: average
    sql: (${miles} * 2) ;;
  }
}

- view: delivery_distance
  derived_table:
    sql: SELECT
so.id as store_order_id
, so.status
, so.store_id
, s.internal_name
, so.order_id
, s.latitude
, s.longitude
, o.delivery_latitude
, o.delivery_longitude
,  degrees(acos(sin(radians(s.latitude)) * sin(radians(o.delivery_latitude)) + cos(radians(s.latitude)) * cos(radians(o.delivery_latitude)) * cos(radians(s.longitude - o.delivery_longitude)))) * 60 * 1.1515 as miles
FROM orders o
JOIN orders so ON o.id = so.order_id
JOIN stores s ON s.id = so.store_id
ORDER BY so.id
  fields:
  - dimension: store_order_id
    type: number
    primary_key: true
    sql: ${TABLE}.store_order_id
  - dimension: store_id
    type: number
    sql: ${TABLE}.store_id
  - dimension: internal_name
    sql: ${TABLE}.internal_name
  - dimension: order_id
    type: number
    sql: ${TABLE}.order_id
  - dimension: latitude
    type: number
    sql: ${TABLE}.latitude
  - dimension: longitude
    type: number
    sql: ${TABLE}.longitude
  - dimension: delivery_latitude
    sql: ${TABLE}.delivery_latitude
  - dimension: delivery_longitude
    sql: ${TABLE}.delivery_longitude
  - dimension: status
    sql: {% parameter state_6 %}SE
  WHEN ${TABLE}.status = 6 THEN {% parameter order_status_completed %}
  WHEN ${TABLE}.status = 7 THEN 'Voided'
  WHEN ${TABLE}.status = 49 THEN 'Placed Gift'
  WHEN ${TABLE}.status = 50 THEN 'Scheduled Not Delivered'
ELSE 'NA'
END
  - dimension: miles
    type: number
    sql: ${TABLE}.miles
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_distance_traveled
    type: sum
    label: Total Linear Distance Traveled
    sql: ${miles} * 2
  - measure: avg_distance_traveled
    type: average
    label: Avg Linear Distance Traveled Per Order
    sql: (${miles} * 2)