Interview Question Bank · Half-open windows · LEFT JOIN · FILTER · NULLIF
11. Late arrivals per load batch
HardData engineeringQuestion #251
Concept: Half-open windows · LEFT JOIN · FILTER · NULLIFshow
A batch owns the events loaded during its window, window_start <= loaded_at < window_end; the strict < at the end keeps an event on the boundary from landing in two batches. An event is late when it happened before its batch's window opened. Start from the batch table with a LEFT JOIN so an empty batch still shows up, and count a joined column rather than * so it reports 0.
Example (different data):
SELECT s.shift_id, COUNT(c.call_id) AS calls, COUNT(c.call_id) FILTER (WHERE c.placed_at < s.shift_start) AS carried_over FROM shifts s LEFT JOIN calls c ON c.answered_at >= s.shift_start AND c.answered_at < s.shift_end GROUP BY s.shift_id;
Your task
Fernhill loads mobile events into the warehouse in hourly batches (de_load_batches). Phones that were offline upload old events later, and the team wants to know how much of each batch is late data that earlier reports missed.
For every batch, return batch_id, events_loaded (events whose loaded_at falls in the batch window), late_events (those whose event_time is before window_start), late_pct (late as a % of loaded, rounded to 1 decimal; NULL for a batch that loaded nothing) and max_lateness_minutes (minutes from the earliest late event_time to window_start, rounded to a whole number; NULL when there are no late events). Sort by batch_id.
Starting Postgres in your browser…
Checking your access…