Quick-commerce delivery SLA by dark store: percentiles, not averages

-- Synthetic orders from three dark stores on one evening (not real platform data)
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY, store TEXT, placed_at TEXT, promised_min INTEGER,
  packed_at TEXT, rider_picked_at TEXT, delivered_at TEXT, status TEXT);

WITH RECURSIVE k(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM k WHERE i<600),
base AS (
  SELECT i,
         CASE i % 3 WHEN 0 THEN 'DS-North' WHEN 1 THEN 'DS-Central' ELSE 'DS-East' END AS store,
         datetime('2026-09-26 17:00', '+' || ((i * 17) % 300) || ' minutes') AS placed_at,
         CASE WHEN (i * 7) % 10 < 6 THEN 10 ELSE 15 END AS promised_min,
         2 + ((i / 3) * 11) % 3 AS pick_min,
         (i * 13) % 7 AS travel_extra
  FROM k),
timed AS (
  SELECT *,
         -- DS-Central runs short of riders between 19:00 and 21:00
         CASE WHEN store = 'DS-Central' AND time(placed_at) BETWEEN '19:00' AND '21:00'
              THEN 3 + (i * 5) % 8 ELSE ((i / 3) * 5) % 3 END AS wait_min,
         -- DS-East serves a wider radius
         CASE WHEN store = 'DS-East' THEN 5 ELSE 2 END + travel_extra * promised_min / 15 AS travel_min
  FROM base)
INSERT INTO orders
SELECT i, store, placed_at, promised_min,
       datetime(placed_at, '+' || pick_min || ' minutes'),
       datetime(placed_at, '+' || (pick_min + wait_min) || ' minutes'),
       CASE WHEN i % 40 = 0 THEN NULL
            ELSE datetime(placed_at, '+' || (pick_min + wait_min + travel_min) || ' minutes') END,
       CASE WHEN i % 40 = 0 THEN 'cancelled' ELSE 'delivered' END
FROM timed;

-- one rider phone with a clock 4 minutes slow stamps two pickups before packing
UPDATE orders SET rider_picked_at = datetime(rider_picked_at, '-4 minutes') WHERE order_id IN (102, 201);

-- julianday differences carry float noise: round to hundredths of a minute
CREATE VIEW d AS
SELECT order_id, store, promised_min, status,
       ROUND((julianday(delivered_at) - julianday(placed_at)) * 1440, 2) AS total_min,
       ROUND((julianday(packed_at) - julianday(placed_at)) * 1440, 2) AS pick_min,
       ROUND((julianday(rider_picked_at) - julianday(packed_at)) * 1440, 2) AS wait_min,
       ROUND((julianday(delivered_at) - julianday(rider_picked_at)) * 1440, 2) AS travel_min
FROM orders;

-- data checks first
SELECT status, COUNT(*) AS orders,
       SUM(wait_min < 0) AS negative_rider_wait
FROM d GROUP BY status;

WITH ranked AS (
  SELECT store, total_min, promised_min,
         ROW_NUMBER() OVER (PARTITION BY store ORDER BY total_min) AS rn,
         COUNT(*) OVER (PARTITION BY store) AS n
  FROM d WHERE status = 'delivered')
SELECT store, n AS delivered,
       MAX(CASE WHEN rn = CAST(0.5 * n + 0.999999 AS INTEGER) THEN total_min END) AS p50_min,
       MAX(CASE WHEN rn = CAST(0.9 * n + 0.999999 AS INTEGER) THEN total_min END) AS p90_min,
       ROUND(AVG(total_min), 1) AS mean_min,
       ROUND(100.0 * AVG(total_min <= promised_min), 1) AS on_promise_pct,
       ROUND(100.0 * AVG(total_min <= 10), 1) AS within_10_min_pct
FROM ranked GROUP BY store ORDER BY on_promise_pct;

-- where the late minutes go: stage medians for late versus on-time orders
WITH s AS (
  SELECT store, pick_min, MAX(wait_min, 0) AS wait_min, travel_min,
         CASE WHEN total_min <= promised_min THEN 'on time' ELSE 'late' END AS outcome
  FROM d WHERE status = 'delivered')
SELECT store, outcome, COUNT(*) AS orders,
       ROUND(AVG(pick_min), 1) AS avg_pick, ROUND(AVG(wait_min), 1) AS avg_rider_wait,
       ROUND(AVG(travel_min), 1) AS avg_travel
FROM s GROUP BY store, outcome ORDER BY store, outcome;

-- on-promise rate by the promise made
SELECT store, promised_min, COUNT(*) AS delivered,
       ROUND(100.0 * AVG(total_min <= promised_min), 1) AS on_promise_pct
FROM d WHERE status = 'delivered' GROUP BY store, promised_min ORDER BY store, promised_min;

-- DS-Central by hour of order
SELECT strftime('%H:00', o.placed_at) AS hour, COUNT(*) AS delivered,
       ROUND(100.0 * AVG(d.total_min <= d.promised_min), 1) AS on_promise_pct,
       ROUND(AVG(MAX(d.wait_min, 0)), 1) AS avg_rider_wait
FROM d JOIN orders o USING (order_id)
WHERE d.store = 'DS-Central' AND d.status = 'delivered'
GROUP BY hour ORDER BY hour;