SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 151 of 250

Hard

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

Table:shop_orders
idcustomer_idordered_onchannelstatustotal_cents
20112025-01-04onlinecompleted2500
20222025-01-10storecompleted1800
20322025-01-10storecompleted3200
20432025-01-18marketplacerefunded5000
20542025-02-02storecompleted2700
20612025-02-04onlinecompleted4000
20752025-02-18onlinecancelled2400
20862025-02-28onlinecompleted1700
20942025-03-16onlinecompleted2900
21082025-03-09storecompleted3100

Expected output

order_datecompleted_orders
2025-01-010
2025-01-020
2025-01-030
2025-01-060
2025-01-070
2025-01-080
2025-01-090
2025-01-102
2025-01-130
2025-01-140
2025-01-150
2025-01-160
2025-01-170
2025-01-200
2025-01-210
2025-01-220
2025-01-230
2025-01-240
2025-01-270
2025-01-280
2025-01-290
2025-01-300
2025-01-310
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;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…