// advent-of-postgres/day-3
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.
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.
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