Account Shipment Companyery Times

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

SELECT metros.id AS metro_id,
       metros.name AS metro_name,
       accounts.name AS account_name,
       malls.owner AS mall_owner,
       malls.name AS mall_name,
       stores.internal_name AS store_name,
       orders.order_number,
       orders.tracking_code,
       orders.status AS order_status,
       shipment_companyeries.status AS shipment_companyery_status,
       time_windows.starts_at AS shipment_companyery_window_starts_at,
       time_windows.ends_at AS shipment_companyery_window_ends_at,
       orders.created_at AS order_created_at,
       orders.canceled_at AS order_canceled_at,
       shipment_companyeries.window_chosen_at AS shipment_companyery_window_chosen_at,
       shipment_companyeries.created_at AS shipment_companyery_created_at,
       shipment_companyeries.store_time_bound AS shipment_companyery_scheduled_pickup,
       shipment_companyeries.assigned_at AS shipment_companyery_assigned_at,
       sweepuptasks.actual_start_at AS sweep_up_started_at,
       TIMESTAMPDIFF(minute,sweepuptasks.actual_start_at,sweepuptasks.completed_at) AS sweep_up_wait_time,
       sweepuptasks.completed_at AS sweep_up_completed_at,
       pickuptasks.actual_start_at AS pick_up_started_at,
       TIMESTAMPDIFF(minute,pickuptasks.actual_start_at,pickuptasks.completed_at) AS pick_up_wait_time,
       pickuptasks.completed_at AS pick_up_completed_at,
       shipment_companyeries.pickup_at AS shipment_companyery_picked_up_at,
       TIMESTAMPDIFF(minute,shipment_companyeries.pickup_at,shipment_companyeries.shipment_companyered_at) AS time_to_shipment_companyer,
       shipment_companyeries.shipment_companyered_at AS shipment_companyery_shipment_companyered_at,
       TIMESTAMPDIFF(minute,time_windows.ends_at,shipment_companyeries.shipment_companyered_at) AS minutes_from_deadline,
       drive_users.first_name AS driver_first_name,
       drive_users.last_name AS driver_last_name,
       exception_entries.created_at AS exception_created_at,
       excp_rpt_users.first_name AS exception_user_first_name,
       excp_rpt_users.last_name AS exception_user_last_name,
       exception_nodes.description AS exception_node,
       exception_types.description AS exception_type,
       exception_entries.comments AS exception_comments,
       lateness_root_causes.created_at AS lateness_root_created_at,
       late_rpt_users.first_name AS lateness_root_first_name,
       late_rpt_users.last_name AS lateness_root_last_name,
       lateness_reasons.description AS lateness_reason,
       lateness_root_causes.explanation AS lateness_root_explanation,
       started_tasks.order_id is not null AS tasks_started_flag,
       lateness_reasons.id AS lateness_reason_id
  FROM metros,
       accounts,
       stores LEFT
  JOIN malls
    ON malls.id=stores.mall_id,
       orders
  JOIN shipment_companyeries
    ON shipment_companyeries.order_id = orders.id LEFT
  JOIN exception_entries
    ON exception_entries.exceptionable_id=orders.id LEFT
  JOIN exception_types
    ON exception_types.id=exception_entries.exception_type_id LEFT
  JOIN exception_nodes
    ON exception_nodes.id=exception_types.exception_node_id LEFT
  JOIN users excp_rpt_users
    ON excp_rpt_users.id=exception_entries.user_id LEFT
  JOIN lateness_root_causes
    ON lateness_root_causes.transport_id=shipment_companyeries.id LEFT
  JOIN lateness_reasons
    ON lateness_reasons.id=lateness_root_causes.lateness_reason_id LEFT
  JOIN users late_rpt_users
    ON late_rpt_users.id=lateness_root_causes.reporting_user_id LEFT
  JOIN tasks AS pickuptasks
    ON pickuptasks.taskable_id=shipment_companyeries.id
   AND pickuptasks.task_type_id = 2
   AND pickuptasks.taskable_type='<% parameter placeholder_task_type %>' LEFT
  JOIN (select sweeps.order_id,
               tasks.actual_start_at,
               tasks.completed_at
          from sweeps,
               tasks
         where sweeps.id=tasks.taskable_id
           and tasks.task_type_id = 5
           and tasks.taskable_type='BaseSweep') AS sweepuptasks
    ON sweepuptasks.order_id=orders.id LEFT
  JOIN (SELECT shipment_companyeries.order_id
          FROM tasks,
               shipment_companyeries
         WHERE tasks.taskable_id = shipment_companyeries.id
           AND tasks.taskable_type = '<% parameter placeholder_task_type %>'
           AND tasks.actual_start_at is not null
         UNION
        SELECT sweeps.order_id
          FROM tasks,
               sweeps
         WHERE tasks.taskable_id = sweeps.id
           AND tasks.taskable_type = 'BaseSweep'
           AND tasks.actual_start_at is not null) AS started_tasks
    ON started_tasks.order_id = orders.id LEFT
  JOIN time_windows
    ON time_windows.id=shipment_companyeries.time_window_id LEFT
  JOIN users drive_users
    ON drive_users.id=shipment_companyeries.driver_id
 WHERE orders.store_id=stores.id
   AND orders.account_id=accounts.id
   AND orders.metro_id=metros.id
   AND accounts.id in (<% parameter sanitized_account_id2 %>, <% parameter sanitized_account_id1 %>)
 ORDER BY shipment_companyeries.created_at DESC

# models/schema.yml
version: 2

models:
  - name: account_shipment_companyery_times
    columns:
      - name: metro_name
      - name: metro_id
      - name: market
      - name: account_name
      - name: mall_owner
      - name: mall_name
      - name: store_name
      - name: tracking_code
      - name: order_status
      - name: shipment_companyery_status
      - name: order_canceled_at
      - name: tasks_started_flag
      - name: sweep_up_wait_time
      - name: pick_up_wait_time
      - name: time_to_shipment_companyer
      - name: minutes_from_deadline
      - name: driver_first_name
      - name: driver_last_name
      - name: exception_user_first_name
      - name: exception_user_last_name
      - name: exception_node
      - name: exception_type
      - name: exception_comments
      - name: lateness_root_created_at
      - name: lateness_root_first_name
      - name: lateness_root_last_name
      - name: lateness_reason
      - name: lateness_root_explanation
      - name: lateness_reason_id
      - name: carrier_caused_lateness
      - name: non_carrier_caused_lateness

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 metros.id AS metro_id,
       metros.name AS metro_name,
       accounts.name AS account_name,
       malls.owner AS mall_owner,
       malls.name AS mall_name,
       stores.internal_name AS store_name,
       orders.order_number,
       orders.tracking_code,
       orders.status AS order_status,
       shipment_companyeries.status AS shipment_companyery_status,
       time_windows.starts_at AS shipment_companyery_window_starts_at,
       time_windows.ends_at AS shipment_companyery_window_ends_at,
       orders.created_at AS order_created_at,
       orders.canceled_at AS order_canceled_at,
       shipment_companyeries.window_chosen_at AS shipment_companyery_window_chosen_at,
       shipment_companyeries.created_at AS shipment_companyery_created_at,
       shipment_companyeries.store_time_bound AS shipment_companyery_scheduled_pickup,
       shipment_companyeries.assigned_at AS shipment_companyery_assigned_at,
       sweepuptasks.actual_start_at AS sweep_up_started_at,
       TIMESTAMPDIFF(minute,sweepuptasks.actual_start_at,sweepuptasks.completed_at) AS sweep_up_wait_time,
       sweepuptasks.completed_at AS sweep_up_completed_at,
       pickuptasks.actual_start_at AS pick_up_started_at,
       TIMESTAMPDIFF(minute,pickuptasks.actual_start_at,pickuptasks.completed_at) AS pick_up_wait_time,
       pickuptasks.completed_at AS pick_up_completed_at,
       shipment_companyeries.pickup_at AS shipment_companyery_picked_up_at,
       TIMESTAMPDIFF(minute,shipment_companyeries.pickup_at,shipment_companyeries.shipment_companyered_at) AS time_to_shipment_companyer,
       shipment_companyeries.shipment_companyered_at AS shipment_companyery_shipment_companyered_at,
       TIMESTAMPDIFF(minute,time_windows.ends_at,shipment_companyeries.shipment_companyered_at) AS minutes_from_deadline,
       drive_users.first_name AS driver_first_name,
       drive_users.last_name AS driver_last_name,
       exception_entries.created_at AS exception_created_at,
       excp_rpt_users.first_name AS exception_user_first_name,
       excp_rpt_users.last_name AS exception_user_last_name,
       exception_nodes.description AS exception_node,
       exception_types.description AS exception_type,
       exception_entries.comments AS exception_comments,
       lateness_root_causes.created_at AS lateness_root_created_at,
       late_rpt_users.first_name AS lateness_root_first_name,
       late_rpt_users.last_name AS lateness_root_last_name,
       lateness_reasons.description AS lateness_reason,
       lateness_root_causes.explanation AS lateness_root_explanation,
       started_tasks.order_id is not null AS tasks_started_flag,
       lateness_reasons.id AS lateness_reason_id
  FROM metros,
       accounts,
       stores LEFT
  JOIN malls
    ON malls.id=stores.mall_id,
       orders
  JOIN shipment_companyeries
    ON shipment_companyeries.order_id = orders.id LEFT
  JOIN exception_entries
    ON exception_entries.exceptionable_id=orders.id LEFT
  JOIN exception_types
    ON exception_types.id=exception_entries.exception_type_id LEFT
  JOIN exception_nodes
    ON exception_nodes.id=exception_types.exception_node_id LEFT
  JOIN users excp_rpt_users
    ON excp_rpt_users.id=exception_entries.user_id LEFT
  JOIN lateness_root_causes
    ON lateness_root_causes.transport_id=shipment_companyeries.id LEFT
  JOIN lateness_reasons
    ON lateness_reasons.id=lateness_root_causes.lateness_reason_id LEFT
  JOIN users late_rpt_users
    ON late_rpt_users.id=lateness_root_causes.reporting_user_id LEFT
  JOIN tasks AS pickuptasks
    ON pickuptasks.taskable_id=shipment_companyeries.id
   AND pickuptasks.task_type_id = 2
   AND pickuptasks.taskable_type='<% parameter placeholder_task_type %>' LEFT
  JOIN (select sweeps.order_id,
               tasks.actual_start_at,
               tasks.completed_at
          from sweeps,
               tasks
         where sweeps.id=tasks.taskable_id
           and tasks.task_type_id = 5
           and tasks.taskable_type='BaseSweep') AS sweepuptasks
    ON sweepuptasks.order_id=orders.id LEFT
  JOIN (SELECT shipment_companyeries.order_id
          FROM tasks,
               shipment_companyeries
         WHERE tasks.taskable_id = shipment_companyeries.id
           AND tasks.taskable_type = '<% parameter placeholder_task_type %>'
           AND tasks.actual_start_at is not null
         UNION
        SELECT sweeps.order_id
          FROM tasks,
               sweeps
         WHERE tasks.taskable_id = sweeps.id
           AND tasks.taskable_type = 'BaseSweep'
           AND tasks.actual_start_at is not null) AS started_tasks
    ON started_tasks.order_id = orders.id LEFT
  JOIN time_windows
    ON time_windows.id=shipment_companyeries.time_window_id LEFT
  JOIN users drive_users
    ON drive_users.id=shipment_companyeries.driver_id
 WHERE orders.store_id=stores.id
   AND orders.account_id=accounts.id
   AND orders.metro_id=metros.id
   AND accounts.id in (<% parameter sanitized_account_id2 %>, <% parameter sanitized_account_id1 %>)
 ORDER BY shipment_companyeries.created_at DESC

view: account_shipment_companyery_times {
  derived_table: {
    sql: SELECT metros.id AS metro_id,
       metros.name AS metro_name,
       accounts.name AS account_name,
       malls.owner AS mall_owner,
       malls.name AS mall_name,
       stores.internal_name AS store_name,
       orders.order_number,
       orders.tracking_code,
       orders.status AS order_status,
       shipment_companyeries.status AS shipment_companyery_status,
       time_windows.starts_at AS shipment_companyery_window_starts_at,
       time_windows.ends_at AS shipment_companyery_window_ends_at,
       orders.created_at AS order_created_at,
       orders.canceled_at AS order_canceled_at,
       shipment_companyeries.window_chosen_at AS shipment_companyery_window_chosen_at,
       shipment_companyeries.created_at AS shipment_companyery_created_at,
       shipment_companyeries.store_time_bound AS shipment_companyery_scheduled_pickup,
       shipment_companyeries.assigned_at AS shipment_companyery_assigned_at,
       sweepuptasks.actual_start_at AS sweep_up_started_at,
       TIMESTAMPDIFF(minute,sweepuptasks.actual_start_at,sweepuptasks.completed_at) AS sweep_up_wait_time,
       sweepuptasks.completed_at AS sweep_up_completed_at,
       pickuptasks.actual_start_at AS pick_up_started_at,
       TIMESTAMPDIFF(minute,pickuptasks.actual_start_at,pickuptasks.completed_at) AS pick_up_wait_time,
       pickuptasks.completed_at AS pick_up_completed_at,
       shipment_companyeries.pickup_at AS shipment_companyery_picked_up_at,
       TIMESTAMPDIFF(minute,shipment_companyeries.pickup_at,shipment_companyeries.shipment_companyered_at) AS time_to_shipment_companyer,
       shipment_companyeries.shipment_companyered_at AS shipment_companyery_shipment_companyered_at,
       TIMESTAMPDIFF(minute,time_windows.ends_at,shipment_companyeries.shipment_companyered_at) AS minutes_from_deadline,
       drive_users.first_name AS driver_first_name,
       drive_users.last_name AS driver_last_name,
       exception_entries.created_at AS exception_created_at,
       excp_rpt_users.first_name AS exception_user_first_name,
       excp_rpt_users.last_name AS exception_user_last_name,
       exception_nodes.description AS exception_node,
       exception_types.description AS exception_type,
       exception_entries.comments AS exception_comments,
       lateness_root_causes.created_at AS lateness_root_created_at,
       late_rpt_users.first_name AS lateness_root_first_name,
       late_rpt_users.last_name AS lateness_root_last_name,
       lateness_reasons.description AS lateness_reason,
       lateness_root_causes.explanation AS lateness_root_explanation,
       started_tasks.order_id is not null AS tasks_started_flag,
       lateness_reasons.id AS lateness_reason_id
  FROM metros,
       accounts,
       stores LEFT
  JOIN malls
    ON malls.id=stores.mall_id,
       orders
  JOIN shipment_companyeries
    ON shipment_companyeries.order_id = orders.id LEFT
  JOIN exception_entries
    ON exception_entries.exceptionable_id=orders.id LEFT
  JOIN exception_types
    ON exception_types.id=exception_entries.exception_type_id LEFT
  JOIN exception_nodes
    ON exception_nodes.id=exception_types.exception_node_id LEFT
  JOIN users excp_rpt_users
    ON excp_rpt_users.id=exception_entries.user_id LEFT
  JOIN lateness_root_causes
    ON lateness_root_causes.transport_id=shipment_companyeries.id LEFT
  JOIN lateness_reasons
    ON lateness_reasons.id=lateness_root_causes.lateness_reason_id LEFT
  JOIN users late_rpt_users
    ON late_rpt_users.id=lateness_root_causes.reporting_user_id LEFT
  JOIN tasks AS pickuptasks
    ON pickuptasks.taskable_id=shipment_companyeries.id
   AND pickuptasks.task_type_id = 2
   AND pickuptasks.taskable_type='<% parameter placeholder_task_type %>' LEFT
  JOIN (select sweeps.order_id,
               tasks.actual_start_at,
               tasks.completed_at
          from sweeps,
               tasks
         where sweeps.id=tasks.taskable_id
           and tasks.task_type_id = 5
           and tasks.taskable_type='BaseSweep') AS sweepuptasks
    ON sweepuptasks.order_id=orders.id LEFT
  JOIN (SELECT shipment_companyeries.order_id
          FROM tasks,
               shipment_companyeries
         WHERE tasks.taskable_id = shipment_companyeries.id
           AND tasks.taskable_type = '<% parameter placeholder_task_type %>'
           AND tasks.actual_start_at is not null
         UNION
        SELECT sweeps.order_id
          FROM tasks,
               sweeps
         WHERE tasks.taskable_id = sweeps.id
           AND tasks.taskable_type = 'BaseSweep'
           AND tasks.actual_start_at is not null) AS started_tasks
    ON started_tasks.order_id = orders.id LEFT
  JOIN time_windows
    ON time_windows.id=shipment_companyeries.time_window_id LEFT
  JOIN users drive_users
    ON drive_users.id=shipment_companyeries.driver_id
 WHERE orders.store_id=stores.id
   AND orders.account_id=accounts.id
   AND orders.metro_id=metros.id
   AND accounts.id in (<% parameter sanitized_account_id2 %>, <% parameter sanitized_account_id1 %>)
 ORDER BY shipment_companyeries.created_at DESC ;;
  }

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

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

  dimension: market {
  }

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

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

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

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

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

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

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

  dimension: order_canceled_at {
    type: time
    sql: ${TABLE}.order_canceled_at ;;
  }

  dimension: tasks_started_flag {
    type: yesno
    sql: ${TABLE}.tasks_started_flag ;;
  }

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

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

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

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

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

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

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

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

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

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

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

  dimension: lateness_root_created_at {
    type: time
    sql: ${TABLE}.lateness_root_created_at ;;
  }

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

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

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

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

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

  dimension: carrier_caused_lateness {
    type: yesno
  }

  dimension: non_carrier_caused_lateness {
    type: yesno
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

- view: account_shipment_companyery_times
  derived_table:
    sql: SELECT metros.id AS metro_id,
       metros.name AS metro_name,
       accounts.name AS account_name,
       malls.owner AS mall_owner,
       malls.name AS mall_name,
       stores.internal_name AS store_name,
       orders.order_number,
       orders.tracking_code,
       orders.status AS order_status,
       shipment_companyeries.status AS shipment_companyery_status,
       time_windows.starts_at AS shipment_companyery_window_starts_at,
       time_windows.ends_at AS shipment_companyery_window_ends_at,
       orders.created_at AS order_created_at,
       orders.canceled_at AS order_canceled_at,
       shipment_companyeries.window_chosen_at AS shipment_companyery_window_chosen_at,
       shipment_companyeries.created_at AS shipment_companyery_created_at,
       shipment_companyeries.store_time_bound AS shipment_companyery_scheduled_pickup,
       shipment_companyeries.assigned_at AS shipment_companyery_assigned_at,
       sweepuptasks.actual_start_at AS sweep_up_started_at,
       TIMESTAMPDIFF(minute,sweepuptasks.actual_start_at,sweepuptasks.completed_at) AS sweep_up_wait_time,
       sweepuptasks.completed_at AS sweep_up_completed_at,
       pickuptasks.actual_start_at AS pick_up_started_at,
       TIMESTAMPDIFF(minute,pickuptasks.actual_start_at,pickuptasks.completed_at) AS pick_up_wait_time,
       pickuptasks.completed_at AS pick_up_completed_at,
       shipment_companyeries.pickup_at AS shipment_companyery_picked_up_at,
       TIMESTAMPDIFF(minute,shipment_companyeries.pickup_at,shipment_companyeries.shipment_companyered_at) AS time_to_shipment_companyer,
       shipment_companyeries.shipment_companyered_at AS shipment_companyery_shipment_companyered_at,
       TIMESTAMPDIFF(minute,time_windows.ends_at,shipment_companyeries.shipment_companyered_at) AS minutes_from_deadline,
       drive_users.first_name AS driver_first_name,
       drive_users.last_name AS driver_last_name,
       exception_entries.created_at AS exception_created_at,
       excp_rpt_users.first_name AS exception_user_first_name,
       excp_rpt_users.last_name AS exception_user_last_name,
       exception_nodes.description AS exception_node,
       exception_types.description AS exception_type,
       exception_entries.comments AS exception_comments,
       lateness_root_causes.created_at AS lateness_root_created_at,
       late_rpt_users.first_name AS lateness_root_first_name,
       late_rpt_users.last_name AS lateness_root_last_name,
       lateness_reasons.description AS lateness_reason,
       lateness_root_causes.explanation AS lateness_root_explanation,
       started_tasks.order_id is not null AS tasks_started_flag,
       lateness_reasons.id AS lateness_reason_id
  FROM metros,
       accounts,
       stores LEFT
  JOIN malls
    ON malls.id=stores.mall_id,
       orders
  JOIN shipment_companyeries
    ON shipment_companyeries.order_id = orders.id LEFT
  JOIN exception_entries
    ON exception_entries.exceptionable_id=orders.id LEFT
  JOIN exception_types
    ON exception_types.id=exception_entries.exception_type_id LEFT
  JOIN exception_nodes
    ON exception_nodes.id=exception_types.exception_node_id LEFT
  JOIN users excp_rpt_users
    ON excp_rpt_users.id=exception_entries.user_id LEFT
  JOIN lateness_root_causes
    ON lateness_root_causes.transport_id=shipment_companyeries.id LEFT
  JOIN lateness_reasons
    ON lateness_reasons.id=lateness_root_causes.lateness_reason_id LEFT
  JOIN users late_rpt_users
    ON late_rpt_users.id=lateness_root_causes.reporting_user_id LEFT
  JOIN tasks AS pickuptasks
    ON pickuptasks.taskable_id=shipment_companyeries.id
   AND pickuptasks.task_type_id = 2
   AND pickuptasks.taskable_type='<% parameter placeholder_task_type %>' LEFT
  JOIN (select sweeps.order_id,
               tasks.actual_start_at,
               tasks.completed_at
          from sweeps,
               tasks
         where sweeps.id=tasks.taskable_id
           and tasks.task_type_id = 5
           and tasks.taskable_type='BaseSweep') AS sweepuptasks
    ON sweepuptasks.order_id=orders.id LEFT
  JOIN (SELECT shipment_companyeries.order_id
          FROM tasks,
               shipment_companyeries
         WHERE tasks.taskable_id = shipment_companyeries.id
           AND tasks.taskable_type = '<% parameter placeholder_task_type %>'
           AND tasks.actual_start_at is not null
         UNION
        SELECT sweeps.order_id
          FROM tasks,
               sweeps
         WHERE tasks.taskable_id = sweeps.id
           AND tasks.taskable_type = 'BaseSweep'
           AND tasks.actual_start_at is not null) AS started_tasks
    ON started_tasks.order_id = orders.id LEFT
  JOIN time_windows
    ON time_windows.id=shipment_companyeries.time_window_id LEFT
  JOIN users drive_users
    ON drive_users.id=shipment_companyeries.driver_id
 WHERE orders.store_id=stores.id
   AND orders.account_id=accounts.id
   AND orders.metro_id=metros.id
   AND accounts.id in (<% parameter sanitized_account_id2 %>, <% parameter sanitized_account_id1 %>)
 ORDER BY shipment_companyeries.created_at DESC
  fields:
  - dimension: metro_name
    sql: ${TABLE}.metro_name
  - dimension: metro_id
    sql: ${TABLE}.metro_id
  - dimension: market
  - dimension: account_name
    sql: ${TABLE}.account_name
  - dimension: mall_owner
    sql: ${TABLE}.mall_owner
  - dimension: mall_name
    sql: ${TABLE}.mall_name
  - dimension: store_name
    sql: ${TABLE}.store_name
  - dimension: tracking_code
    sql: ${TABLE}.tracking_code
  - dimension: order_status
    sql: ${TABLE}.order_status
  - dimension: shipment_companyery_status
    sql: ${TABLE}.shipment_companyery_status
  - dimension: order_canceled_at
    type: time
    sql: ${TABLE}.order_canceled_at
  - dimension: tasks_started_flag
    type: yesno
    sql: ${TABLE}.tasks_started_flag
  - dimension: sweep_up_wait_time
    type: number
    sql: ${TABLE}.sweep_up_wait_time
  - dimension: pick_up_wait_time
    type: number
    sql: ${TABLE}.pick_up_wait_time
  - dimension: time_to_shipment_companyer
    type: number
    sql: ${TABLE}.time_to_shipment_companyer
  - dimension: minutes_from_deadline
    type: number
    sql: ${TABLE}.minutes_from_deadline
  - dimension: driver_first_name
    sql: ${TABLE}.driver_first_name
  - dimension: driver_last_name
    sql: ${TABLE}.driver_last_name
  - dimension: exception_user_first_name
    sql: ${TABLE}.exception_user_first_name
  - dimension: exception_user_last_name
    sql: ${TABLE}.exception_user_last_name
  - dimension: exception_node
    sql: ${TABLE}.exception_node
  - dimension: exception_type
    sql: ${TABLE}.exception_type
  - dimension: exception_comments
    sql: ${TABLE}.exception_comments
  - dimension: lateness_root_created_at
    type: time
    sql: ${TABLE}.lateness_root_created_at
  - dimension: lateness_root_first_name
    sql: ${TABLE}.lateness_root_first_name
  - dimension: lateness_root_last_name
    sql: ${TABLE}.lateness_root_last_name
  - dimension: lateness_reason
    sql: ${TABLE}.lateness_reason
  - dimension: lateness_root_explanation
    sql: ${TABLE}.lateness_root_explanation
  - dimension: lateness_reason_id
    sql: ${TABLE}.lateness_reason_id
  - dimension: carrier_caused_lateness
    type: yesno
  - dimension: non_carrier_caused_lateness
    type: yesno
  - dimension_group: shipment_companyery_window_starts_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_window_starts_at
  - dimension_group: shipment_companyery_window_ends_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_window_ends_at
  - dimension_group: order_created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.order_created_at
  - dimension_group: shipment_companyery_window_chosen_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_window_chosen_at
  - dimension_group: shipment_companyery_created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_created_at
  - dimension_group: shipment_companyery_scheduled_pickup
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_scheduled_pickup
  - dimension_group: shipment_companyery_assigned_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_assigned_at
  - dimension_group: sweep_up_started_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.sweep_up_started_at
  - dimension_group: sweep_up_completed_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.sweep_up_completed_at
  - dimension_group: pick_up_started_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.pick_up_started_at
  - dimension_group: pick_up_completed_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.pick_up_completed_at
  - dimension_group: shipment_companyery_picked_up_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_picked_up_at
  - dimension_group: shipment_companyery_shipment_companyered_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.shipment_companyery_shipment_companyered_at
  - dimension_group: exception_created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.exception_created_at
  - measure: count
    type: count
    drill_fields:
    - detail*