Latest Device State

Support needs a device snapshot at 2025-06-01 00:00:00+00. Create a view named result from:

device_events(event_id INT PRIMARY KEY, device_id TEXT NOT NULL, customer_id INT NOT NULL, status TEXT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL)

Contract

Return device_id, customer_id, last_status, last_seen_at, ordered by device_id.

• Keep only events at or before the inclusive cutoff above.
• Select one event per device: greatest recorded_at, then greatest event_id if timestamps tie. This tie policy is part of the exercise.
• All returned attributes must come from that selected event. Rename status to last_status and recorded_at to last_seen_at.
• A device with no qualifying event does not appear. Empty input produces no rows.

Trace before coding

One device has event 31 (offline, customer 4) at 23:50, event 32 (online, customer 4) at midnight, event 33 (error, customer 9) at midnight, and event 34 (online, customer 9) one minute after midnight. Its output uses event 33, including customer 9. Event 34 is ineligible; event 33 wins the time tie.

Hints, in increasing detail

1. Decide which rows may participate before choosing the winner.
2. Rank per device by recency and the stated tie breaker.
3. Filter on the rank in an outer query. A final output sort cannot affect the row chosen inside the window.

Explain why ranking all history and then removing future rows would lose this device, and why independent maxima of each output column can invent a row.