Show which sources contain each channel
Your task
Return every distinct channel appearing in either receipts or shop_orders after trimming spaces and lowercasing. Show channel, in_receipts, and in_orders as 1 or 0 flags. Sort by channel.
A full outer join keeps values even if they occur on only one side.
Sample data
| id | sold_on | amount | channel | metadata |
|---|---|---|---|---|
| 1 | 2025-01-01 | 36 | ONLINE | {"currency":"EUR","tags":["gift","jazz"]} |
| 2 | 2025-01-16 | 22 | Store | {"currency":"EUR","tags":["indie"]} |
| 3 | 2025-01-31 | 54 | online | {"currency":"EUR","tags":["returning","bundle"]} |
| 4 | 2025-03-04 | 38 | STORE | {"currency":"EUR","tags":[]} |
| 5 | 2025-04-09 | 44 | Online | {"currency":"EUR","tags":["indie"]} |
Expected output
| channel | in_receipts | in_orders |
|---|---|---|
| marketplace | 0 | 1 |
| online | 1 | 1 |
| store | 1 | 1 |
Show a hint
Deduplicate each source before a FULL OUTER JOIN. Use COALESCE to get the channel from either side.
Joins and set operations guide
Match rows by a key
A join combines columns horizontally. The condition links sales.album_id to albums.id. If an album has three sale records, a join can return its title three times.
SELECT s.id AS sale_id, a.title, s.quantity
FROM sales AS s
JOIN albums AS a ON a.id = s.album_id;Preserve rows without a match
A LEFT JOIN retains each album. The quantity condition belongs in ON because it defines which sales qualify as matches. Placing it in WHERE would also remove albums whose sale columns are NULL.
SELECT a.title, s.id AS large_sale_id
FROM albums AS a
LEFT JOIN sales AS s
ON s.album_id = a.id
AND s.quantity >= 3;Combine result sets vertically
UNION stacks compatible result sets and removes duplicate rows. UNION ALL keeps every row. INTERSECT keeps shared rows, and EXCEPT keeps rows unique to the first result. With set operations, both inputs must return the same number of columns.
SELECT title FROM albums WHERE genre = 'Jazz'
UNION
SELECT title FROM albums WHERE price < 20;Worked solution
WITH r AS (SELECT DISTINCT LOWER(TRIM(channel)) AS channel FROM receipts), o AS (SELECT DISTINCT LOWER(TRIM(channel)) AS channel FROM shop_orders) SELECT COALESCE(r.channel,o.channel) AS channel, r.channel IS NOT NULL AS in_receipts, o.channel IS NOT NULL AS in_orders FROM r FULL OUTER JOIN o ON r.channel=o.channel ORDER BY channel;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
