Skip to main content

// advent-of-postgres/day-7

Day 7 of 24IntermediateDISTINCT ONDeduplication

The Duplicate Events

The queue delivers some events twice. Find the latest status of every order before you add anything up.

Opens 7 December 2026, 00:00 UTC

The gift dashboard says the workshop shipped far more than the warehouse remembers. Shinny suspects the event queue: when a consumer times out, the queue delivers the same event again.

Every event is in order_events:

Column Type Notes
event_id bigint Primary key
order_id int The order the event is about
status text placed, packed, shipped or cancelled
gift_value int The order's value, in candy canes
received_at timestamptz When the event arrived

An order's current status is the status of its most recent event. A duplicate has the same order and status as another event.

Your task

What is the total gift value of all orders whose current status is shipped?

Submit the number.

Stuck? Show a hint

DISTINCT ON keeps the first row of each group in the ORDER BY order, so order the newest event first.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.