Interview Question Bank · generate_series · LATERAL latest-before · carry forward
12. Daily snapshot from status events
HardData engineeringQuestion #252
Concept: generate_series · LATERAL latest-before · carry forwardshow
Event tables record only changes, so a day with no events still has a state: whatever the last change before it set. Build the calendar with generate_series, cross join it with every entity, and use LEFT JOIN LATERAL to fetch each entity's latest event before the end of that day (changed_at < day + 1 day, ORDER BY changed_at DESC LIMIT 1). Entities with no event yet don't exist on that day.
Example (different data):
SELECT d::date AS day, r.room, s.state FROM generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-01-07', INTERVAL '1 day') AS d CROSS JOIN rooms r LEFT JOIN LATERAL ( SELECT state FROM room_log l WHERE l.room = r.room AND l.logged_at < d + INTERVAL '1 day' ORDER BY l.logged_at DESC LIMIT 1 ) s ON true;
Your task
Fernhill's orchestration tool writes a row to de_pipeline_events whenever a pipeline is switched between active and paused. The on-call lead wants a daily snapshot of how many pipelines were running at the end of each day.
For each day from 2025-08-01 to 2025-08-07, return snapshot_date, active_pipelines and paused_pipelines, counting each pipeline by the status set by its last change on or before the end of that day. Pipelines with no change yet don't count. Sort by snapshot_date.
Starting Postgres in your browser…
Checking your access…