Dark Store Daily Orders, Zero Days Included — SQL Problem
The launch report for the new store 7 shows one row for every day from 1 to 14 June 2024, including days with no orders.
- Difficulty: Hard
- Topics: Dates, Window Functions
- Dialect: MySQL
- Problem: #169
Problem statement
The launch report for the new store 7 shows one row for every day from 1 to 14 June 2024, including days with no orders. An order counts on the date of its ordered_at unless it was cancelled.
Return order_date (YYYY-MM-DD), orders (that day's non-cancelled store-7 orders, 0 when none) and cumulative_orders (the running total from 1 June up to and including that day). Exactly 14 rows, ordered by order_date.
Tables
Table: QuickOrder
| Column | Type |
|---|---|
| order_id | int |
| store_id | int |
| ordered_at | datetime |
| status | enum(delivered, cancelled, returned) |
Primary key: order_id.
One row per grocery order across all dark stores. A returned order was delivered and still counts.
Examples
Example 1
QuickOrder
| order_id | store_id | ordered_at | status |
|---|---|---|---|
| 1 | 7 | 2024-06-01 08:10:00 | delivered |
| 2 | 7 | 2024-06-01 21:45:00 | delivered |
| 3 | 7 | 2024-06-03 07:30:00 | cancelled |
| 4 | 5 | 2024-06-03 09:00:00 | delivered |
| 5 | 7 | 2024-06-04 23:59:00 | returned |
| 6 | 7 | 2024-06-09 12:00:00 | delivered |
| 7 | 7 | 2024-06-14 18:20:00 | delivered |
| 8 | 7 | 2024-06-15 00:05:00 | delivered |
| 9 | 7 | 2024-05-31 22:00:00 | delivered |
Output
| order_date | orders | cumulative_orders |
|---|---|---|
| 2024-06-01 | 2 | 2 |
| 2024-06-02 | 0 | 2 |
| 2024-06-03 | 0 | 2 |
| 2024-06-04 | 1 | 3 |
| 2024-06-05 | 0 | 3 |
| 2024-06-06 | 0 | 3 |
| 2024-06-07 | 0 | 3 |
| 2024-06-08 | 0 | 3 |
| 2024-06-09 | 1 | 4 |
| 2024-06-10 | 0 | 4 |
| 2024-06-11 | 0 | 4 |
| 2024-06-12 | 0 | 4 |
| 2024-06-13 | 0 | 4 |
| 2024-06-14 | 1 | 5 |
How to solve Dark Store Daily Orders, Zero Days Included
GROUP BY can only report the dates that have rows, and a day with no orders has none, so the dates must come from somewhere else. A recursive CTE builds them: the anchor is 1 June, the recursive step adds one day with DATE_ADD(d, INTERVAL 1 DAY), and WHERE d < '2024-06-14' stops it after 14 rows.
LEFT JOIN the orders onto that calendar. The conditions that pick orders — store 7, not cancelled, placed on that day — go in the ON clause; in WHERE they would drop the NULL-extended rows of empty days. Matching a day as the half-open range [d, d + 1 day) keeps 23:59 on the 4th on the 4th and 00:05 on the 15th out of the window. COUNT(o.order_id) counts only real matches, so an empty day is 0.
The running total is a window over the 14 daily rows: SUM(orders) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Correlated counts per day — that day's orders, and all orders from 1 June to that day — give the same numbers by re-scanning, which is quadratic but tiny for 14 days. The recursion is bounded by its own WHERE, so it always terminates.
Reference solution (MySQL)
WITH RECURSIVE days AS (
SELECT CAST('2024-06-01' AS DATE) AS d
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d < '2024-06-14'
), daily AS (
SELECT days.d AS order_date, COUNT(o.order_id) AS orders
FROM days
LEFT JOIN QuickOrder o
ON o.store_id = 7 AND o.status <> 'cancelled'
AND o.ordered_at >= days.d AND o.ordered_at < DATE_ADD(days.d, INTERVAL 1 DAY)
GROUP BY days.d
)
SELECT order_date, orders,
SUM(orders) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_orders
FROM daily
ORDER BY order_dateAnother way
WITH RECURSIVE days AS (
SELECT 0 AS k UNION ALL SELECT k + 1 FROM days WHERE k < 13
), counted AS (
SELECT CAST(ordered_at AS DATE) AS dt FROM QuickOrder WHERE store_id = 7 AND status IN ('delivered', 'returned')
)
SELECT DATE_ADD('2024-06-01', INTERVAL k DAY) AS order_date,
(SELECT COUNT(*) FROM counted c WHERE c.dt = DATE_ADD('2024-06-01', INTERVAL k DAY)) AS orders,
(SELECT COUNT(*) FROM counted c WHERE c.dt BETWEEN '2024-06-01' AND DATE_ADD('2024-06-01', INTERVAL k DAY)) AS cumulative_orders
FROM days
ORDER BY order_date← Double-Logged Rider Shifts That Overlap · Food App Checkout Funnel, Step by Step →