On-Call Engineers Who Were Not Paged in March — SQL Problem
The platform team reviews its pager load once a month. An alert routed to a team alias rather than a person has engineer_id NULL and belongs to nobody.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #329
Problem statement
The platform team reviews its pager load once a month. An alert routed to a team alias rather than a person has engineer_id NULL and belongs to nobody.
Return every engineer who received no page during March 2025 (from 2025-03-01 00:00:00 up to, not including, 2025-04-01 00:00:00), with the columns engineer_id and name. Pages in other months do not matter. Return the rows in any order.
Tables
Table: Engineer
| Column | Type |
|---|---|
| engineer_id | int |
| name | varchar |
| team | varchar |
Primary key: engineer_id.
One row per engineer in the on-call rotation.
Table: PageAlert
| Column | Type |
|---|---|
| alert_id | int |
| engineer_id | int |
| service | varchar |
| paged_at | datetime |
Primary key: alert_id.
One row per page sent. engineer_id is the person paged, or NULL when the page went to a team alias.
Examples
Example 1
Engineer
| engineer_id | name | team |
|---|---|---|
| 1 | Rohan | Payments |
| 2 | Aditi | Payments |
| 3 | Vikram | Search |
| 4 | Zara | Search |
| 5 | Harsh | Platform |
PageAlert
| alert_id | engineer_id | service | paged_at |
|---|---|---|---|
| 9001 | 1 | payments-svc | 2025-03-04 03:12:00 |
| 9002 | 2 | payments-svc | 2025-02-28 23:50:00 |
| 9003 | 3 | search-svc | 2025-04-01 00:00:00 |
| 9004 | NULL | auth-svc | 2025-03-15 14:00:00 |
| 9005 | 1 | checkout-api | 2025-03-20 09:30:00 |
| 9006 | 4 | search-svc | 2025-03-31 23:59:00 |
Output
| engineer_id | name |
|---|---|
| 2 | Aditi |
| 3 | Vikram |
| 5 | Harsh |
How to solve On-Call Engineers Who Were Not Paged in March
The answer is the engineers with no matching row among March's pages: an anti join. The LEFT JOIN form joins every engineer to their pages from March only and keeps the engineers for whom nothing matched, detected by the page's primary key being NULL.
Two details decide whether it is right. The month filter must be part of the ON clause: in the WHERE clause it would run after the join and drop the NULL rows — exactly the engineers without March pages. And the month is a half-open range, >= '2025-03-01 00:00:00' AND < '2025-04-01 00:00:00', so a page at midnight on April 1st belongs to April and one at 23:59 on March 31st to March; BETWEEN '2025-03-01' AND '2025-03-31' would miss everything after midnight on the 31st.
NOT EXISTS with YEAR and MONTH says the same thing per engineer. NOT IN works only after filtering out the NULL engineer_id of team-alias pages: one NULL in the list makes x NOT IN (…) unknown for everyone, and the answer would come back empty. With an index on (engineer_id, paged_at) each form is one range probe per engineer.
Reference solution (MySQL)
SELECT e.engineer_id, e.name
FROM Engineer e
LEFT JOIN PageAlert p
ON p.engineer_id = e.engineer_id
AND p.paged_at >= '2025-03-01 00:00:00'
AND p.paged_at < '2025-04-01 00:00:00'
WHERE p.alert_id IS NULLAnother way
SELECT engineer_id, name FROM Engineer e WHERE NOT EXISTS (SELECT 1 FROM PageAlert p WHERE p.engineer_id = e.engineer_id AND YEAR(p.paged_at) = 2025 AND MONTH(p.paged_at) = 3)Another way
SELECT engineer_id, name FROM Engineer WHERE engineer_id NOT IN (SELECT engineer_id FROM PageAlert WHERE engineer_id IS NOT NULL AND DATE_FORMAT(paged_at, '%Y-%m') = '2025-03')← Production Incidents Opened on a Weekend · Label CSAT Survey Responses by Sentiment →