Production Incidents Opened on a Weekend — SQL Dates Problem
The SRE manager is making the case for a paid weekend on-call rota and wants the incidents that were opened on a Saturday or a Sunday (by the date part of…
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #328
Problem statement
The SRE manager is making the case for a paid weekend on-call rota and wants the incidents that were opened on a Saturday or a Sunday (by the date part of opened_at; a Friday 23:59 page is still a weekday incident).
Return the columns incident_id, title and opened_day — the weekday's English name, 'Saturday' or 'Sunday' — ordered by opened_at, then incident_id.
Tables
Table: Incident
| Column | Type |
|---|---|
| incident_id | int |
| title | varchar |
| severity | enum(SEV1, SEV2, SEV3) |
| opened_at | datetime |
Primary key: incident_id.
One row per production incident, with the moment it was declared.
Examples
Example 1
Incident
| incident_id | title | severity | opened_at |
|---|---|---|---|
| 41 | Checkout latency above 2s | SEV2 | 2025-02-28 23:59:00 |
| 42 | UPI callbacks failing | SEV1 | 2025-03-01 02:15:00 |
| 43 | Search index stale | SEV3 | 2025-03-02 18:40:00 |
| 44 | Login OTP delays | SEV2 | 2025-03-03 00:00:00 |
| 45 | Notification queue backlog | SEV3 | 2025-03-05 11:20:00 |
| 46 | Payments DB failover | SEV1 | 2025-03-08 07:05:00 |
Output
| incident_id | title | opened_day |
|---|---|---|
| 42 | UPI callbacks failing | Saturday |
| 43 | Search index stale | Sunday |
| 46 | Payments DB failover | Saturday |
How to solve Production Incidents Opened on a Weekend
The question is about the weekday of each opened_at, which a date function answers directly. DAYOFWEEK(d) numbers the days from Sunday = 1 to Saturday = 7, so the weekend is IN (1, 7); WEEKDAY(d) numbers them from Monday = 0 to Sunday = 6, so the weekend is >= 5; and DAYNAME(d) gives the English name, which is also what the output column wants. Any of the three works as the filter — just don't mix up the two numbering schemes, the most common bug with these functions.
The functions look only at the date part, so an incident at Friday 23:59 is a Friday incident and one at Monday 00:00 a Monday incident, exactly as the statement asks.
Two incidents can be declared in the same minute, so the order uses incident_id after opened_at. A function on the column prevents an index range scan, but on an incidents table the full scan is cheap.
Reference solution (MySQL)
SELECT incident_id, title, DAYNAME(opened_at) AS opened_day
FROM Incident
WHERE DAYOFWEEK(opened_at) IN (1, 7)
ORDER BY opened_at, incident_idAnother way
SELECT incident_id, title, DAYNAME(opened_at) AS opened_day FROM Incident WHERE WEEKDAY(opened_at) >= 5 ORDER BY opened_at, incident_idAnother way
SELECT incident_id, title, DAYNAME(opened_at) AS opened_day FROM Incident WHERE DAYNAME(opened_at) IN ('Saturday', 'Sunday') ORDER BY opened_at, incident_id← Error Codes in the API Gateway Logs · On-Call Engineers Who Were Not Paged in March →