// advent-of-postgres/day-6
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.
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.
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.
We earn commissions when you shop through the links below.
Bitrise
Managed runners for GitHub Actions
Bitrise Build Hub runs your GitHub Actions jobs on M4 Macs and AMD Linux machines. Change runs-on and keep your workflows, secrets and logs on GitHub.
Atomsized
AWS platform engineering and GitOps
Design and automation for reliable AWS and Kubernetes platforms, safer delivery workflows, and preview and UAT environments your engineers can understand and own.
DigitalOcean
Cloud infrastructure for developers
Simple, reliable cloud computing designed for developers
DevDojo
Developer community & tools
Join a community of developers sharing knowledge and tools
SMTPfast
Developer-first email API
Send transactional and marketing email through a clean REST API. Detailed logs, webhooks, and embeddable signup forms in one dashboard.
QuizAPI
Developer-first quiz platform
Build, generate, and embed quizzes with a powerful REST API. AI-powered question generation and live multiplayer.
Want to support DevOps Daily and reach thousands of developers?
Become a Sponsor