Fill weekday order counts with zero
Your task
Return order_date and completed_orders for every Monday through Friday in January 2025. Show zero on weekdays without completed orders. Sort by date.
A calendar supplies the dates a transaction table cannot show on its own.
Sample data
shop_orders| id | customer_id | ordered_on | channel | status | total_cents |
|---|---|---|---|---|---|
| 201 | 1 | 2025-01-04 | online | completed | 2500 |
| 202 | 2 | 2025-01-10 | store | completed | 1800 |
| 203 | 2 | 2025-01-10 | store | completed | 3200 |
| 204 | 3 | 2025-01-18 | marketplace | refunded | 5000 |
| 205 | 4 | 2025-02-02 | store | completed | 2700 |
| 206 | 1 | 2025-02-04 | online | completed | 4000 |
| 207 | 5 | 2025-02-18 | online | cancelled | 2400 |
| 208 | 6 | 2025-02-28 | online | completed | 1700 |
| 209 | 4 | 2025-03-16 | online | completed | 2900 |
| 210 | 8 | 2025-03-09 | store | completed | 3100 |
Expected output
| order_date | completed_orders |
|---|---|
| 2025-01-01 | 0 |
| 2025-01-02 | 0 |
| 2025-01-03 | 0 |
| 2025-01-06 | 0 |
| 2025-01-07 | 0 |
| 2025-01-08 | 0 |
| 2025-01-09 | 0 |
| 2025-01-10 | 2 |
| 2025-01-13 | 0 |
| 2025-01-14 | 0 |
| 2025-01-15 | 0 |
| 2025-01-16 | 0 |
| 2025-01-17 | 0 |
| 2025-01-20 | 0 |
| 2025-01-21 | 0 |
| 2025-01-22 | 0 |
| 2025-01-23 | 0 |
| 2025-01-24 | 0 |
| 2025-01-27 | 0 |
| 2025-01-28 | 0 |
| 2025-01-29 | 0 |
| 2025-01-30 | 0 |
| 2025-01-31 | 0 |
Show a hint
Generate January dates recursively, left join completed orders by date, and use STRFTIME('%w', date) to keep weekdays.
Dates, text, and JSON guide
Work with dates as ISO text
The receipt dates use YYYY-MM-DD, so their text order matches calendar order. Use an inclusive start and exclusive end for periods. This query returns receipts in the first quarter, without including April 1.
SELECT id, sold_on, amount
FROM receipts
WHERE sold_on >= '2025-01-01'
AND sold_on < '2025-04-01';Normalize values before comparing them
The receipt channel values vary in capitalization and spacing. TRIM removes surrounding spaces and LOWER handles these ASCII names. Use the same normalization when grouping values that represent the same category.
SELECT LOWER(TRIM(channel)) AS channel, COUNT(*) AS receipts
FROM receipts
GROUP BY LOWER(TRIM(channel));Extract a field or expand an array
JSON_EXTRACT reads a value at a path, such as the currency code. JSON_EACH turns an array into rows with a value column. After expansion, receipt IDs can repeat and a receipt with no tags contributes no rows.
SELECT r.id, JSON_EXTRACT(r.metadata, '$.currency') AS currency,
tag.value AS tag
FROM receipts AS r
CROSS JOIN JSON_EACH(r.metadata, '$.tags') AS tag;Worked solution
WITH RECURSIVE days(day) AS (VALUES('2025-01-01') UNION ALL SELECT DATE(day,'+1 day') FROM days WHERE day<'2025-01-31') SELECT d.day AS order_date,COUNT(o.id) AS completed_orders FROM days d LEFT JOIN shop_orders o ON o.ordered_on=d.day AND o.status='completed' WHERE STRFTIME('%w',d.day) BETWEEN '1' AND '5' GROUP BY d.day ORDER BY d.day;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
