Production Incidents Opened on a Weekend

Easy
328.

Production Incidents Opened on a Weekend

EasyDatesBasics

MySQL

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

ColumnTypeValues
incident_idint
titlevarchar
severityenumSEV1, SEV2, SEV3
opened_atdatetime

Primary key: incident_id.

One row per production incident, with the moment it was declared.

Example

The rows must come back in the order shown.

Incident

incident_idtitleseverityopened_at
41Checkout latency above 2sSEV22025-02-28 23:59:00
42UPI callbacks failingSEV12025-03-01 02:15:00
43Search index staleSEV32025-03-02 18:40:00
44Login OTP delaysSEV22025-03-03 00:00:00
45Notification queue backlogSEV32025-03-05 11:20:00
46Payments DB failoverSEV12025-03-08 07:05:00

Output

incident_idtitleopened_day
42UPI callbacks failingSaturday
43Search index staleSunday
46Payments DB failoverSaturday
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.

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

ColumnType
incident_idint
titlevarchar
severityenum(SEV1, SEV2, SEV3)
opened_atdatetime

Primary key: incident_id.

One row per production incident, with the moment it was declared.

Examples

Example 1

Incident

incident_idtitleseverityopened_at
41Checkout latency above 2sSEV22025-02-28 23:59:00
42UPI callbacks failingSEV12025-03-01 02:15:00
43Search index staleSEV32025-03-02 18:40:00
44Login OTP delaysSEV22025-03-03 00:00:00
45Notification queue backlogSEV32025-03-05 11:20:00
46Payments DB failoverSEV12025-03-08 07:05:00

Output

incident_idtitleopened_day
42UPI callbacks failingSaturday
43Search index staleSunday
46Payments DB failoverSaturday

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_id

Another way

SELECT incident_id, title, DAYNAME(opened_at) AS opened_day FROM Incident WHERE WEEKDAY(opened_at) >= 5 ORDER BY opened_at, incident_id

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