On-Call Engineers Who Were Not Paged in March

Easy
329.

On-Call Engineers Who Were Not Paged in March

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

ColumnType
engineer_idint
namevarchar
teamvarchar

Primary key: engineer_id.

One row per engineer in the on-call rotation.

Table: PageAlert

ColumnType
alert_idint
engineer_idint
servicevarchar
paged_atdatetime

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Engineer

engineer_idnameteam
1RohanPayments
2AditiPayments
3VikramSearch
4ZaraSearch
5HarshPlatform

PageAlert

alert_idengineer_idservicepaged_at
90011payments-svc2025-03-04 03:12:00
90022payments-svc2025-02-28 23:50:00
90033search-svc2025-04-01 00:00:00
9004NULLauth-svc2025-03-15 14:00:00
90051checkout-api2025-03-20 09:30:00
90064search-svc2025-03-31 23:59:00

Output

engineer_idname
2Aditi
3Vikram
5Harsh
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
engineer_idint
namevarchar
teamvarchar

Primary key: engineer_id.

One row per engineer in the on-call rotation.

Table: PageAlert

ColumnType
alert_idint
engineer_idint
servicevarchar
paged_atdatetime

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_idnameteam
1RohanPayments
2AditiPayments
3VikramSearch
4ZaraSearch
5HarshPlatform

PageAlert

alert_idengineer_idservicepaged_at
90011payments-svc2025-03-04 03:12:00
90022payments-svc2025-02-28 23:50:00
90033search-svc2025-04-01 00:00:00
9004NULLauth-svc2025-03-15 14:00:00
90051checkout-api2025-03-20 09:30:00
90064search-svc2025-03-31 23:59:00

Output

engineer_idname
2Aditi
3Vikram
5Harsh

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 NULL

Another 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 →