Skip to main content

// advent-of-postgres/day-16

Day 16 of 24Intermediatemode()Two-level aggregation

The Error of the Hour

One bad hour can make an error code look like the biggest problem of the day. Find the code that leads the most hours instead.

Opens 16 December 2026, 00:00 UTC

The error dashboard shows one huge bar for 16 December, and Bushy wants to fix that error first. Tinsel is not so sure. "One bad hour does not make a pattern," Tinsel says. "Which error is the problem hour after hour?"

Every error on 16 December is in errors:

Column Type Notes
id bigint Primary key
occurred_at timestamptz When it happened
code text For example E429 or E503

For each hour of the day (UTC), the error of the hour is the code that occurred most often in that hour. If two codes tie in an hour, the code that sorts first alphabetically wins that hour.

Your task

Which code was the error of the hour in the most hours?

Submit the code.

Stuck? Show a hint

mode() WITHIN GROUP (ORDER BY code) gives the most common code per group, and picks the code that sorts first on a tie. Group by date_trunc('hour', occurred_at, 'UTC') first, then count the winners.

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.