SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 150 of 250

Hard

Count distinct receipts carrying each tag

Your task

Return every tag that appears in metadata.tags and the number of distinct receipts carrying it as receipt_count. Sort by receipt_count descending, then tag alphabetically.

Treat a repeated tag within one receipt as a single receipt-tag match.

Sample data

Table:receipts
idsold_onamountchannelmetadata
12025-01-0136 ONLINE {"currency":"EUR","tags":["gift","jazz"]}
22025-01-1622Store{"currency":"EUR","tags":["indie"]}
32025-01-3154 online{"currency":"EUR","tags":["returning","bundle"]}
42025-03-0438STORE {"currency":"EUR","tags":[]}
52025-04-0944Online{"currency":"EUR","tags":["indie"]}

Expected output

tagreceipt_count
indie2
bundle1
gift1
jazz1
returning1
Show a hint

Expand the arrays with JSON_EACH and count distinct receipt IDs for each expanded value.

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…