Approach: bucket time into 15-minute intervals, compute supply = count of distinct drivers online during each interval, demand = count of ride_requests in that interval with state='requested' (or unmet if state indicates unfulfilled). Gap = demand - supply (only positive gaps). Filter to last week and return top 3 per city.sql
WITH params AS (
SELECT
date_trunc('week', current_date) - interval '7 days' AS week_start,
date_trunc('week', current_date) - interval '1 second' AS week_end
),
intervals AS (
-- generate 15-min intervals for the week
SELECT generate_series(week_start, week_end, interval '15 minutes') AS interval_start
FROM params
),
driver_presence AS (
-- expand driver active periods into interval buckets (can be optimized)
SELECT
d.driver_id,
d.city,
i.interval_start,
i.interval_start + interval '15 minutes' AS interval_end
FROM drivers d
JOIN intervals i
ON d.online_at < i.interval_start + interval '15 minutes'
AND COALESCE(d.offline_at, now()) > i.interval_start
JOIN params p ON d.city IS NOT NULL
WHERE d.online_at <= p.week_end AND COALESCE(d.offline_at, now()) >= p.week_start
),
supply AS (
SELECT city, interval_start, COUNT(DISTINCT driver_id) AS supply
FROM driver_presence
GROUP BY city, interval_start
),
demand AS (
SELECT city, date_trunc('minute', requested_at) -
(EXTRACT(minute FROM requested_at)::int % 15) * interval '1 minute' AS interval_start,
COUNT(*) AS demand
FROM ride_requests r, params p
WHERE requested_at BETWEEN p.week_start AND p.week_end
GROUP BY city, interval_start
),
combined AS (
SELECT
COALESCE(d.city, s.city) AS city,
COALESCE(d.interval_start, s.interval_start) AS interval_start,
COALESCE(d.demand,0) AS demand,
COALESCE(s.supply,0) AS supply,
GREATEST(COALESCE(d.demand,0) - COALESCE(s.supply,0),0) AS unmet_gap
FROM demand d
FULL OUTER JOIN supply s USING (city, interval_start)
)
SELECT city, interval_start, unmet_gap
FROM combined
ORDER BY unmet_gap DESC
LIMIT 3;
