Moderation Reports Filed on Weekends in March 2025
Trust and safety staffs a smaller team on weekends and wants to see the weekend load. Find the reports filed on a Saturday or Sunday during March 2025.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #428
Problem statement
Trust and safety staffs a smaller team on weekends and wants to see the weekend load. Find the reports filed on a Saturday or Sunday during March 2025.
Return the columns report_id, reason and day_name (the full English weekday name, such as Saturday), ordered by reported_at, then by report_id.
Tables
Table: reports
| Column | Type |
|---|---|
| report_id | int |
| post_id | int |
| reporter_id | int |
| reason | enum(spam, harassment, hate_speech, nudity, misinformation, impersonation) |
| reported_at | datetime |
Primary key: report_id.
One row per user report against a post, with the time it was filed (IST).
Examples
Example 1
reports
| report_id | post_id | reporter_id | reason | reported_at |
|---|---|---|---|---|
| 1 | 501 | 31 | spam | 2025-02-28 23:50:00 |
| 2 | 502 | 32 | harassment | 2025-03-01 10:15:00 |
| 3 | 503 | 33 | spam | 2025-03-03 09:00:00 |
| 4 | 504 | 31 | misinformation | 2025-03-09 22:40:00 |
| 5 | 505 | 34 | nudity | 2025-03-15 07:05:00 |
| 6 | 506 | 35 | hate_speech | 2025-03-21 18:30:00 |
| 7 | 507 | 32 | spam | 2025-04-05 11:00:00 |
Output
| report_id | reason | day_name |
|---|---|---|
| 2 | harassment | Saturday |
| 4 | misinformation | Sunday |
| 5 | nudity | Saturday |
How to solve Moderation Reports Filed on Weekends in March 2025
Two independent filters do the work. The month is best written as a half-open range on the raw column, reported_at >= '2025-03-01' AND reported_at < '2025-04-01': it includes every second of 31 March and keeps the column bare, so an index on reported_at can be used. YEAR(...) = 2025 AND MONTH(...) = 3 or DATE_FORMAT(..., '%Y-%m') = '2025-03' are correct too, but wrap the column in a function.
The weekend test needs a day-of-week function, and the numbering differs between them: DAYOFWEEK gives 1 for Sunday through 7 for Saturday, so the weekend is IN (1, 7); WEEKDAY gives 0 for Monday through 6 for Sunday, so it is >= 5. DAYNAME returns the name itself and doubles as the output column.
A report at 23:50 on Friday 28 February is outside the month, and one on Saturday 5 April is outside it too, which is why both filters are needed. Ties on reported_at fall back to report_id.
Reference solution (MySQL)
SELECT report_id, reason, DAYNAME(reported_at) AS day_name
FROM reports
WHERE reported_at >= '2025-03-01' AND reported_at < '2025-04-01'
AND DAYOFWEEK(reported_at) IN (1, 7)
ORDER BY reported_at, report_idAnother way
SELECT report_id, reason, DAYNAME(reported_at) AS day_name FROM reports WHERE DATE_FORMAT(reported_at, '%Y-%m') = '2025-03' AND WEEKDAY(reported_at) >= 5 ORDER BY reported_at, report_idAnother way
SELECT report_id, reason, DAYNAME(reported_at) AS day_name FROM reports WHERE YEAR(reported_at) = 2025 AND MONTH(reported_at) = 3 AND DAYNAME(reported_at) IN ('Saturday', 'Sunday') ORDER BY reported_at, report_id← Handles Breaking the Username Policy · Label Accounts Active, Dormant or Churned →