SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 208 of 250

Hard

Report high-value order share across all months

Your task

For January through April 2025, return month in YYYY-MM format, completed_orders, high_value_orders of at least 3000 cents, and high_value_pct. Show zero counts and NULL percentage for empty months. Round to one decimal and sort chronologically.

A reporting calendar keeps empty months visible and makes the missing denominator explicit.

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

monthcompleted_ordershigh_value_ordershigh_value_pct
2025-013133.3
2025-023133.3
2025-032150
2025-0400NULL
Show a hint

Generate month starts, left join each month’s completed orders, and divide the qualifying count by NULLIF of the order count.

Optimization and capstones guide

Compare equivalent filters

A function on an indexed column can hide the key from a direct range lookup. The two predicates here select the same January dates in this dataset. Compare the plans and look for a constrained SEARCH on the second query.

EXPLAIN QUERY PLAN
SELECT id FROM receipts WHERE STRFTIME('%Y-%m', sold_on) = '2025-01';

EXPLAIN QUERY PLAN
SELECT id FROM receipts
WHERE sold_on >= '2025-01-01' AND sold_on < '2025-02-01';

Continue from a stable key

OFFSET skips entries before the requested page. With keyset pagination, use the last ID from the previous page as a boundary. The order and boundary must use the same key. Use a unique tie-breaker if the leading sort column can repeat.

EXPLAIN QUERY PLAN
SELECT id, title FROM catalog
WHERE id > 500
ORDER BY id
LIMIT 5;

Summarize first, compare second

Start reports by deciding what one result row represents. This report groups receipts by month, then compares monthly totals with LAG. If February has no receipts, the next row compares with January. Generate a calendar CTE and left join it when gaps need to appear explicitly.

WITH monthly AS (
  SELECT STRFTIME('%Y-%m', sold_on) AS month, SUM(amount) AS total
  FROM receipts
  GROUP BY month
)
SELECT month, total, total - LAG(total) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;
SQLiteCtrl / ⌘ + Enter

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

Results

Loading SQLite…