Dark Store Daily Orders, Zero Days Included

Hard
169.

Dark Store Daily Orders, Zero Days Included

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

ColumnTypeValues
order_idint
store_idint
ordered_atdatetime
statusenumdelivered, cancelled, returned

Primary key: order_id.

One row per grocery order across all dark stores. A returned order was delivered and still counts.

Example

The rows must come back in the order shown.

QuickOrder

order_idstore_idordered_atstatus
172024-06-01 08:10:00delivered
272024-06-01 21:45:00delivered
372024-06-03 07:30:00cancelled
452024-06-03 09:00:00delivered
572024-06-04 23:59:00returned
672024-06-09 12:00:00delivered
772024-06-14 18:20:00delivered
872024-06-15 00:05:00delivered
972024-05-31 22:00:00delivered

Output

order_dateorderscumulative_orders
2024-06-0122
2024-06-0202
2024-06-0302
2024-06-0413
2024-06-0503
2024-06-0603
2024-06-0703
2024-06-0803
2024-06-0914
2024-06-1004
4 more rows
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.

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

ColumnType
order_idint
store_idint
ordered_atdatetime
statusenum(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_idstore_idordered_atstatus
172024-06-01 08:10:00delivered
272024-06-01 21:45:00delivered
372024-06-03 07:30:00cancelled
452024-06-03 09:00:00delivered
572024-06-04 23:59:00returned
672024-06-09 12:00:00delivered
772024-06-14 18:20:00delivered
872024-06-15 00:05:00delivered
972024-05-31 22:00:00delivered

Output

order_dateorderscumulative_orders
2024-06-0122
2024-06-0202
2024-06-0302
2024-06-0413
2024-06-0503
2024-06-0603
2024-06-0703
2024-06-0803
2024-06-0914
2024-06-1004
2024-06-1104
2024-06-1204
2024-06-1304
2024-06-1415

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_date

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