March POS Bills by Day of the Week

Easy
379.

March POS Bills by Day of the Week

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

ColumnType
bill_idint
store_idint
billed_atdatetime
amountint

Primary key: bill_id.

One row per paid bill; billed_at is the local time the bill was closed.

Example

The rows must come back in the order shown.

TillBill

bill_idstore_idbilled_atamount
112025-03-01 10:15:00840
222025-03-01 19:40:001260
312025-03-02 11:05:00455
412025-03-08 18:30:002190
532025-03-03 09:50:00310
622025-03-31 23:59:00600
722025-04-01 00:05:00990
832025-02-28 20:10:001500

Output

weekdaybillsrevenue
Saturday34290
Monday2910
Sunday1455
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.

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

ColumnType
bill_idint
store_idint
billed_atdatetime
amountint

Primary key: bill_id.

One row per paid bill; billed_at is the local time the bill was closed.

Examples

Example 1

TillBill

bill_idstore_idbilled_atamount
112025-03-01 10:15:00840
222025-03-01 19:40:001260
312025-03-02 11:05:00455
412025-03-08 18:30:002190
532025-03-03 09:50:00310
622025-03-31 23:59:00600
722025-04-01 00:05:00990
832025-02-28 20:10:001500

Output

weekdaybillsrevenue
Saturday34290
Monday2910
Sunday1455

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, weekday

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