Skip to main content

// advent-of-postgres/day-6

Day 6 of 24IntermediateLAGPARTITION BY

The Quick Rollbacks

A release that goes back to the version before it is a rollback. Count the fast ones, per service, with window functions.

Opens 6 December 2026, 00:00 UTC

Wunorse thinks the team ships too fast. "Half our releases get rolled back before lunch," Wunorse claims. You decide to check.

Every release is in releases:

Column Type Notes
id int Primary key
service text Each service has its own version line
version text For example 4.12
released_at timestamptz When the release went live

For one service, a release is a quick rollback when its version is the same as the version released two releases earlier, and it went out no more than 60 minutes after the release before it. Releases of different services do not affect each other.

Your task

How many quick rollbacks are in the table?

Submit the number.

Stuck? Show a hint

lag(version, 2) and lag(released_at) need a window that is partitioned by service.

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.