A common table expression names a relation within one statement. WITH does not create a persistent table. It helps divide a report into stages with explicit grains: combine channel events, aggregate by day, then present the result. Treat a CTE as a name for a query result, not a promise about when SQLite materializes it.
The sources contain events. A repeated day/user pair can mean that the same user generated two genuine events. Preserve that multiplicity until a metric explicitly requests distinct values.
Follow the data
UNION ALL concatenates the result rows of compatible queries and retains duplicates. UNION removes duplicate complete rows. If both channels contain (day, user_id) = ('2026-01-01', 1), UNION keeps one pair; UNION ALL keeps both events. Neither input order guarantees the final output order.
Set-operation inputs must return the same number of columns with compatible meanings. Align day with day and user_id with user_id explicitly. Column names of a compound query come from its first SELECT in SQLite. INTERSECT selects common distinct rows, while EXCEPT selects distinct rows in the first relation absent from the second. They answer membership questions rather than event-volume questions.
After combining at event grain, GROUP BY day. COUNT(*) measures total events across channels. COUNT(DISTINCT user_id) counts each user once per day across both channels. Adding each channel's distinct-user counts instead would count cross-channel users twice.
CTEs also make a safe boundary for reducing join fanout: aggregate each child relation to one row per parent before joining it. Always state the grain and columns of each intermediate result. A CTE can reference earlier CTEs in the same WITH clause, but its name disappears when the statement ends. Recursive CTEs are a separate tool; this build requires only nonrecursive report composition.
Work a complete example
The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.
CREATE TABLE web_events(day TEXT NOT NULL, user_id INTEGER NOT NULL);
CREATE TABLE app_events(day TEXT NOT NULL, user_id INTEGER NOT NULL);
INSERT INTO web_events VALUES ('2026-01-01',1),('2026-01-01',1),('2026-01-02',2);
INSERT INTO app_events VALUES ('2026-01-01',1),('2026-01-01',3);
WITH all_events AS (
SELECT day, user_id FROM web_events
UNION ALL
SELECT day, user_id FROM app_events
), daily AS (
SELECT day, COUNT(*) AS events, COUNT(DISTINCT user_id) AS users
FROM all_events GROUP BY day
)
SELECT day, events, users FROM daily
ORDER BY day;Diagnose the common failure
Deduplication is a policy decision, not a default cleanup step. The inputs do not contain an event id, so equal day/user values are not evidence of duplicate ingestion. Distinct-user counts can be correct even when the event count is wrong; check both metrics in the same fixture.
Try a change before building
Add one more app event for an existing day/user and one event for a new day. Predict which metrics change. Then replace UNION ALL with UNION and explain why the first change vanishes.
Move into practice
Name intermediate relations, preserve event multiplicity and combine sources before reporting. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.
Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.