DoorDash Business Model & Trade-offs Questions

WITH cleaned AS (
  SELECT
    order_id,
    city,
    created_at,
    accepted_at,
    -- compute time to accept in minutes (float)
    EXTRACT(EPOCH FROM (accepted_at - created_at)) / 60.0 AS time_to_accept_min,
    EXTRACT(HOUR FROM created_at) AS created_hour
  FROM orders
  WHERE
    status != 'cancelled'                 -- exclude cancelled orders
    AND created_at IS NOT NULL            -- need created time
    AND accepted_at IS NOT NULL           -- exclude orders never accepted
),
buckets AS (
  -- define buckets (example: 0-1,1-2,2-5,5-10,10-30,30+ minutes)
  SELECT
    city,
    created_hour,
    time_to_accept_min,
    CASE
      WHEN time_to_accept_min < 0 THEN 'negative'           -- guard against bad data
      WHEN time_to_accept_min < 1 THEN '0-1m'
      WHEN time_to_accept_min < 2 THEN '1-2m'
      WHEN time_to_accept_min < 5 THEN '2-5m'
      WHEN time_to_accept_min < 10 THEN '5-10m'
      WHEN time_to_accept_min < 30 THEN '10-30m'
      ELSE '30m+'
    END AS bucket
  FROM cleaned
),
city_bucket_dist AS (
  -- distribution counts per city and bucket
  SELECT
    city,
    bucket,
    COUNT(*) AS orders_in_bucket,
    COUNT(*)::numeric / SUM(COUNT(*)) OVER (PARTITION BY city) AS pct_of_city
  FROM buckets
  GROUP BY city, bucket
),
median_by_hour AS (
  -- median time_to_accept per city and hour using percentile_cont
  SELECT
    city,
    created_hour,
    percentile_cont(0.5) WITHIN GROUP (ORDER BY time_to_accept_min) AS median_time_to_accept_min,
    COUNT(*) AS orders_count
  FROM cleaned
  GROUP BY city, created_hour
)
-- final unioned result (or return two result sets)
SELECT 'distribution' AS metric, city, bucket AS key, orders_in_bucket, pct_of_city, NULL::numeric AS median_min, NULL::int AS orders_count
FROM city_bucket_dist
UNION ALL
SELECT 'median_by_hour' AS metric, city, created_hour::text AS key, NULL::int AS orders_in_bucket, NULL::numeric AS pct_of_city, median_time_to_accept_min, orders_count
FROM median_by_hour
ORDER BY metric, city, key;