Skip to main content

// advent-of-postgres/day-3

Day 3 of 24BeginnerRange typesJoins

Who Was On Call?

Match every incident to the elf who held the pager when it started. The shifts are ranges, and the handovers are where it gets interesting.

Opens 3 December 2026, 00:00 UTC

The on-call rota has been rough this week, and Pepper wants to thank whoever took the most pages. The rota and the incident list are both in the database, so you offer to work it out.

The rota is in oncall. Each row is one 8-hour shift, stored as a range:

Column Type Notes
elf text Who held the pager
shift tstzrange [start, end): the shift owns its start, not its end

The incidents are in incidents:

Column Type Notes
id int Primary key
service text Where it happened
started_at timestamptz When the first alert fired
severity int 1 is the worst

Shifts hand over at 00:00, 08:00 and 16:00 UTC. Some alerts fire exactly at a handover.

Your task

Count each incident once, for the elf whose shift contains the moment it started. Which elf was on call for the most incidents?

Submit the elf's name.

Stuck? Show a hint

A range has an operator that checks if it contains a timestamp, and it respects the [ ) bounds. BETWEEN does not.

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.