Drill 10 of 12 in Food delivery questions
0

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…