Drill 11 of 12 in Data engineering questions
0

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…