Weekend Food Orders in March 2024 — SQL Dates Problem
Marketing is planning a weekend-only offer and wants to look at last March's weekend traffic first.
- Difficulty: Easy
- Topics: Dates
- Dialect: MySQL
- Problem: #153
Problem statement
Marketing is planning a weekend-only offer and wants to look at last March's weekend traffic first.
Return order_id, placed_at and day_name (the full English weekday name, Saturday or Sunday) for every order placed on a Saturday or a Sunday in March 2024. An order at 23:59 on 31 March still counts; one at 00:00 on 1 April does not. Order the rows by placed_at, then order_id.
Tables
Table: Orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| restaurant_id | int |
| placed_at | datetime |
| order_value | int |
Primary key: order_id.
One row per order; placed_at is the local time the customer paid.
Examples
Example 1
Orders
| order_id | customer_id | restaurant_id | placed_at | order_value |
|---|---|---|---|---|
| 701 | 21 | 3 | 2024-02-25 13:10:00 | 340 |
| 702 | 22 | 1 | 2024-03-02 20:45:00 | 520 |
| 703 | 23 | 2 | 2024-03-04 12:30:00 | 180 |
| 704 | 21 | 3 | 2024-03-17 09:05:00 | 260 |
| 705 | 24 | 1 | 2024-03-31 23:59:00 | 410 |
| 706 | 25 | 2 | 2024-04-01 00:00:00 | 150 |
| 707 | 22 | 2 | 2024-03-02 20:45:00 | 300 |
Output
| order_id | placed_at | day_name |
|---|---|---|
| 702 | 2024-03-02 20:45:00 | Saturday |
| 707 | 2024-03-02 20:45:00 | Saturday |
| 704 | 2024-03-17 09:05:00 | Sunday |
| 705 | 2024-03-31 23:59:00 | Sunday |
How to solve Weekend Food Orders in March 2024
Two date conditions: the month, and the day of the week. For the month, the half-open range placed_at >= '2024-03-01' AND placed_at < '2024-04-01' is the safest form: it includes every time on 31 March (23:59 included) and excludes midnight on 1 April, and it works because datetimes stored as fixed-format text compare in time order. YEAR(…) = 2024 AND MONTH(…) = 3 reads the same but cannot use an index on placed_at; BETWEEN '2024-03-01' AND '2024-03-31' is the classic bug — it stops at the very start of 31 March.
For the weekend, DAYOFWEEK returns 1 for Sunday and 7 for Saturday, so IN (1, 7); WEEKDAY counts from Monday = 0, so >= 5. DAYNAME (or DATE_FORMAT(placed_at, '%W')) prints the name.
Two orders can share a timestamp, so order_id settles the order between them. One pass over the table.
Reference solution (MySQL)
SELECT order_id, placed_at, DAYNAME(placed_at) AS day_name
FROM Orders
WHERE placed_at >= '2024-03-01' AND placed_at < '2024-04-01'
AND DAYOFWEEK(placed_at) IN (1, 7)
ORDER BY placed_at, order_idAnother way
SELECT order_id, placed_at, DATE_FORMAT(placed_at, '%W') AS day_name FROM Orders WHERE YEAR(placed_at) = 2024 AND MONTH(placed_at) = 3 AND WEEKDAY(placed_at) >= 5 ORDER BY placed_at, order_idAnother way
SELECT order_id, placed_at, DAYNAME(placed_at) AS day_name FROM Orders WHERE DATE_FORMAT(placed_at, '%Y-%m') = '2024-03' AND DAYNAME(placed_at) IN ('Saturday', 'Sunday') ORDER BY 2, 1← Masked Customer Phone Numbers on Rider Slips · Restaurants Averaging Under 3.5 Stars →