24_ds009_vs_network.sql

-- Purpose: One-line summary comparing DS009 to the network-wide benchmark -- the
--          headline "problem statement" query for the root-cause investigation.
-- Result: Network avg delivery 22.12 min / 4.13 min delay vs. DS009's 31.69 min / 12.03
--         min delay -- DS009 runs ~43% slower than the network average with no
--         operational cause identified (distance, workload, and picking/packing all
--         ruled out in queries 17, 21-23).

SELECT
  ROUND(AVG(actual_delivery_mins), 2) AS network_avg_delivery_mins,
  ROUND(AVG(delivery_delay_mins), 2) AS network_avg_delay_mins,
  ROUND(
    AVG(CASE WHEN dark_store_id = 'DS009' THEN actual_delivery_mins END), 2
  ) AS ds009_avg_delivery_mins,
  ROUND(
    AVG(CASE WHEN dark_store_id = 'DS009' THEN delivery_delay_mins END), 2
  ) AS ds009_avg_delay_mins
FROM `white-script-418417.quick_commerce.orders`;

/*
Result:
network_avg_delivery_mins  network_avg_delay_mins  ds009_avg_delivery_mins  ds009_avg_delay_mins
22.12                       4.13                     31.69                    12.03
*/