Write SQL for cuisine median delivery times

Use SQL to answer the following. Assume ANSI SQL with window functions and percentile functions available. Treat “today” as 2025-09-01 (inclusive). Compute cuisine-level performance.

Schema and tiny sample data (for illustration only):

restaurants(restaurant_id INT, name TEXT, cuisine TEXT, is_ghost_kitchen BOOLEAN) +---------------+------------+---------+-------------------+ | restaurant_id | name | cuisine | is_ghost_kitchen | +---------------+------------+---------+-------------------+ | 1 | Sushi A | Sushi | false | | 2 | Pizza B | Pizza | false | | 3 | Wings C | American| true | | 4 | Curry D | Indian | false | +---------------+------------+---------+-------------------+

orders(order_id INT, restaurant_id INT, created_at TIMESTAMP, status TEXT, subtotal NUMERIC, city TEXT) +----------+---------------+---------------------+-----------+----------+----------+ | order_id | restaurant_id | created_at | status | subtotal | city | +----------+---------------+---------------------+-----------+----------+----------+ | 101 | 1 | 2025-08-25 18:02:00 | completed | 42.00 | SF | | 102 | 2 | 2025-08-26 19:10:00 | completed | 28.50 | SF | | 103 | 3 | 2025-08-27 12:05:00 | completed | 19.99 | SJ | | 104 | 2 | 2025-08-28 20:14:00 | canceled | 31.00 | SF | | 105 | 4 | 2025-08-31 11:40:00 | completed | 22.00 | OAK | | 106 | 1 | 2025-09-01 13:22:00 | completed | 35.00 | SF | +----------+---------------+---------------------+-----------+----------+----------+

deliveries(delivery_id INT, order_id INT, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, tip NUMERIC, distance_miles NUMERIC) +-------------+----------+---------------------+---------------------+------+----------------+ | delivery_id | order_id | pickup_time | dropoff_time | tip | distance_miles | +-------------+----------+---------------------+---------------------+------+----------------+ | 9001 | 101 | 2025-08-25 18:18:00 | 2025-08-25 18:36:00 | 6.00 | 3.2 | | 9002 | 102 | 2025-08-26 19:22:00 | 2025-08-26 19:52:00 | 5.00 | 7.5 | | 9003 | 103 | 2025-08-27 12:20:00 | 2025-08-27 12:33:00 | 3.00 | 2.1 | | 9004 | 104 | NULL | NULL | NULL | NULL | | 9005 | 105 | 2025-08-31 11:55:00 | 2025-08-31 12:28:00 | 4.00 | 12.0 | | 9006 | 106 | 2025-09-01 13:35:00 | 2025-09-01 13:55:00 | 2.00 | 4.0 | +-------------+----------+---------------------+---------------------+------+----------------+

Task: Write a single SQL query that returns, for each cuisine: (a) median delivery_time_minutes = TIMESTAMP_DIFF(dropoff_time, pickup_time, MINUTE) for completed orders with distance_miles < 10 in the last 7 full days including 2025-09-01 (window A: 2025-08-25 to 2025-09-01), excluding ghost kitchens; (b) the same median for the preceding 7-day window (window B: 2025-08-18 to 2025-08-24); (c) pct_change = (A − B)/NULLIF(B,0); and (d) include only cuisines with at least 30 qualifying deliveries in window A. Output columns: cuisine, recent_median_min, prev_median_min, pct_change, n_recent. Assume UTC timestamps and that canceled orders have no delivery. Do not use temporary tables; use CTEs or subqueries only.