March POS Bills by Day of the Week — SQL Dates Problem
Store managers plan cashier shifts from the weekday pattern of the till. Consider only bills rung up in March 2025 (any store).
- Difficulty: Easy
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #379
Problem statement
Store managers plan cashier shifts from the weekday pattern of the till. Consider only bills rung up in March 2025 (any store).
Return one row per weekday that had bills, with the columns weekday (the full English day name, e.g. Saturday), bills (the number of bills) and revenue (their total amount). Order the rows by bills highest first, then by weekday alphabetically.
Tables
Table: TillBill
| Column | Type |
|---|---|
| bill_id | int |
| store_id | int |
| billed_at | datetime |
| amount | int |
Primary key: bill_id.
One row per paid bill; billed_at is the local time the bill was closed.
Examples
Example 1
TillBill
| bill_id | store_id | billed_at | amount |
|---|---|---|---|
| 1 | 1 | 2025-03-01 10:15:00 | 840 |
| 2 | 2 | 2025-03-01 19:40:00 | 1260 |
| 3 | 1 | 2025-03-02 11:05:00 | 455 |
| 4 | 1 | 2025-03-08 18:30:00 | 2190 |
| 5 | 3 | 2025-03-03 09:50:00 | 310 |
| 6 | 2 | 2025-03-31 23:59:00 | 600 |
| 7 | 2 | 2025-04-01 00:05:00 | 990 |
| 8 | 3 | 2025-02-28 20:10:00 | 1500 |
Output
| weekday | bills | revenue |
|---|---|---|
| Saturday | 3 | 4290 |
| Monday | 2 | 910 |
| Sunday | 1 | 455 |
How to solve March POS Bills by Day of the Week
Two date operations make this query: a range filter for the month and a weekday extraction for the grouping. For the month, a half-open range billed_at >= '2025-03-01' AND billed_at < '2025-04-01' is the safest form for datetimes — BETWEEN '2025-03-01' AND '2025-03-31' would silently drop every bill after midnight on the 31st, because '2025-03-31 23:59:00' is greater than '2025-03-31'. Testing YEAR() and MONTH() is equally correct but cannot use an index on billed_at.
DAYNAME(billed_at) maps each bill to its weekday name; grouping on it gives at most seven rows, each with COUNT(*) and SUM(amount). Weekdays with no bill simply do not appear. Several weekdays can have the same number of bills, so the name is the second sort key.
The cost is one scan of the month plus a tiny grouping step.
Reference solution (MySQL)
SELECT DAYNAME(billed_at) AS weekday,
COUNT(*) AS bills,
SUM(amount) AS revenue
FROM TillBill
WHERE billed_at >= '2025-03-01' AND billed_at < '2025-04-01'
GROUP BY DAYNAME(billed_at)
ORDER BY bills DESC, weekdayAnother way
SELECT DATE_FORMAT(billed_at, '%W') AS weekday, COUNT(*) AS bills, SUM(amount) AS revenue FROM TillBill WHERE YEAR(billed_at) = 2025 AND MONTH(billed_at) = 3 GROUP BY DATE_FORMAT(billed_at, '%W') ORDER BY bills DESC, weekday← Net Stock Change per SKU From the Movement Log · Warehouse Stock Value Including Empty Sites →