Drill 9 of 12 in Logistics questions
0

Interview Question Bank · LAG over two partition keys · interval thresholds

9. Long gaps between a driver's stops

HardLogisticsQuestion #165

Concept: LAG over two partition keys · interval thresholdsshow

LAG(ts) OVER (PARTITION BY ... ORDER BY seq) puts the previous row's value beside the current one, and the partition decides what 'previous' means: a route is a driver on a day, so partition by both. Subtracting timestamps gives an interval, and EXTRACT(MINUTE ...) reads only its minutes field (1 hour 10 minutes gives 10). Compare the interval to INTERVAL '45 minutes' directly, or use EXTRACT(EPOCH ...) / 60 for total minutes.

Example (different data):

WITH t AS (
  SELECT nurse, shift_date, round_no, checked_at,
    checked_at - LAG(checked_at) OVER (PARTITION BY nurse, shift_date ORDER BY round_no) AS gap
  FROM ward_rounds
)
SELECT nurse, shift_date, round_no, EXTRACT(EPOCH FROM gap) / 60 AS gap_min
FROM t WHERE gap > INTERVAL '2 hours';

Your task

Larkspur Freight's last-mile manager wants to find long idle stretches on local delivery routes. A route is one driver's stops on one route_date, worked in stop_seq order.

For every stop after the first on its route, measure the time since the previous stop's arrived_at (same driver, same route_date, previous stop_seq). Return driver, route_date, stop_seq and gap_minutes (total minutes, as a whole number) for gaps of more than 45 minutes. Sort by driver, route_date, stop_seq.

Starting Postgres in your browser…

Checking your access…