Weekend Food Orders in March 2024

Easy
153.

Weekend Food Orders in March 2024

EasyDates

MySQL

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

ColumnType
order_idint
customer_idint
restaurant_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per order; placed_at is the local time the customer paid.

Example

The rows must come back in the order shown.

Orders

order_idcustomer_idrestaurant_idplaced_atorder_value
7012132024-02-25 13:10:00340
7022212024-03-02 20:45:00520
7032322024-03-04 12:30:00180
7042132024-03-17 09:05:00260
7052412024-03-31 23:59:00410
7062522024-04-01 00:00:00150
7072222024-03-02 20:45:00300

Output

order_idplaced_atday_name
7022024-03-02 20:45:00Saturday
7072024-03-02 20:45:00Saturday
7042024-03-17 09:05:00Sunday
7052024-03-31 23:59:00Sunday
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.

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

ColumnType
order_idint
customer_idint
restaurant_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per order; placed_at is the local time the customer paid.

Examples

Example 1

Orders

order_idcustomer_idrestaurant_idplaced_atorder_value
7012132024-02-25 13:10:00340
7022212024-03-02 20:45:00520
7032322024-03-04 12:30:00180
7042132024-03-17 09:05:00260
7052412024-03-31 23:59:00410
7062522024-04-01 00:00:00150
7072222024-03-02 20:45:00300

Output

order_idplaced_atday_name
7022024-03-02 20:45:00Saturday
7072024-03-02 20:45:00Saturday
7042024-03-17 09:05:00Sunday
7052024-03-31 23:59:00Sunday

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_id

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

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