Drill 12 of 12 in Data engineering questions
0

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…