Model schema and query new-market readiness

Assume today is 2025-09-01. You are given (or can propose) a minimal schema to assess new-market readiness and early performance. Use the schema below (feel free to add justified columns if needed) and answer the queries.

Schema (ASCII sample rows included):

cities(city_id, city_name, population, launch_date, tz) 101 | Springfield | 520000 | 2025-08-15 | America/Chicago 102 | Riverton | 180000 | 2025-08-20 | America/Chicago

merchants(merchant_id, city_id, category, is_open, onboard_date) 5001 | 101 | restaurant | true | 2025-08-10 5002 | 101 | grocery | true | 2025-08-12 5003 | 102 | restaurant | false | 2025-08-30

couriers(courier_id, city_id, signup_date, activated_at, status) 9001 | 101 | 2025-08-05 | 2025-08-18 | active 9002 | 101 | 2025-08-20 | null | pending 9003 | 102 | 2025-08-19 | 2025-08-25 | active

orders(order_id, city_id, merchant_id, courier_id, created_at, accepted_at, delivered_at, cancelled, subtotal_cents, delivery_fee_cents, tip_cents) 70001 | 101 | 5001 | 9001 | 2025-08-26 18:02 | 2025-08-26 18:04 | 2025-08-26 18:35 | false | 2800 | 499 | 300 70002 | 101 | 5002 | null | 2025-08-27 12:10 | null | null | true | 1500 | 299 | 0 70003 | 102 | 5003 | 9003 | 2025-08-28 19:20 | 2025-08-28 19:23 | 2025-08-28 20:01 | false | 3200 | 399 | 250

supply_demand(city_id, ts_15min, demand_requests, active_couriers) 101 | 2025-08-26 18:00 | 42 | 35 101 | 2025-08-26 18:15 | 47 | 33 102 | 2025-08-28 19:15 | 28 | 24

Write ANSI SQL for:

1. Last 7 days readiness: For city_id = 101, between 2025-08-26 and 2025-09-01 inclusive, compute per-day counts: orders_created, orders_delivered, orders_cancelled, median ETA minutes for delivered orders (delivered_at - created_at), and merchant coverage = active_merchants_per_10k_pop (is_open = true, onboard_date <= date). Return one row per date with a boolean at_risk if (a) median ETA > 35 or (b) cancel_rate > 8%.
2. Supply–demand risk: For city_id = 101 over the same window, aggregate by day the share of 15-min intervals where active_couriers / NULLIF(demand_requests,0) < 0.8, and flag days where this share > 0.25.
3. Courier activation funnel: For each new courier in city_id = 101 who signed up between 2025-08-15 and 2025-08-31, compute cohort-level rates within 14 days of signup: (i) activated_at present, (ii) completed first delivery, (iii) median time-to-first-delivery (minutes). Return one row with counts and rates.