Interview Question Bank · Self-join · interval overlap · strict boundaries
10. Batched deliveries: overlapping courier jobs
HardFood deliveryQuestion #238
Concept: Self-join · interval overlap · strict boundariesshow
Two time windows overlap when each one starts before the other ends: a.start < b.end AND b.start < a.end. Strict < leaves out windows that merely touch, where one ends the minute the next begins. Self-join on the shared key with a.id < b.id so each pair shows up once, and LEAST of the ends minus GREATEST of the starts is the overlap itself.
Example (different data):
SELECT a.studio, a.id AS first_class, b.id AS second_class, LEAST(a.ends_at, b.ends_at) - GREATEST(a.starts_at, b.starts_at) AS clash FROM classes a JOIN classes b ON b.studio = a.studio AND a.id < b.id AND a.starts_at < b.ends_at AND b.starts_at < a.ends_at;
Your task
Brightbowl lets couriers carry more than one order at a time. Ops wants every pair of jobs a courier was carrying at the same moment, between pickup and drop-off.
Find every pair of jobs in fd_courier_jobs by the same courier whose picked_up_at-to-dropped_at windows overlap (a job dropped off at the exact minute another is picked up does not overlap). List each pair once. Return courier (name), first_job (the smaller job id), second_job and overlap_minutes (length of the overlap in minutes, ROUND(...) to a whole number). Sort by courier, first_job, second_job.
Starting Postgres in your browser…
Checking your access…