SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 202 of 250

Easy

Page through catalog IDs in reverse

Your task

Return the first three catalog IDs strictly below ID 500 when reading from highest to lowest.

When paging in descending order, the next page uses keys below the cursor.

Sample data

Table:catalog
idtitlegenrepricestock
1Record 0001Indie111
2Record 0002Ambient122
3Record 0003Rock133
4Record 0004Jazz144
5Record 0005Indie155
6Record 0006Ambient166
7Record 0007Rock177
8Record 0008Jazz188
9Record 0009Indie190
10Record 0010Ambient201

Preview of 10 of 2048 rows

Expected output

id
499
498
497
Show a hint

Use a key range id < 500 with ORDER BY id DESC and LIMIT 3.

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…