A beginner can copy a grouped query and still be unsure why six input rows became three output rows. For an online SQL lesson, give learners a dataset small enough to sort by hand before they run anything.
This exercise uses SQLite and invented practice attempts. Learners predict groups, check counts and averages, then change which rows or groups survive a filter. Focus Room can set website rules around the browser work period; the SQL editor and teaching conversation remain in your chosen tools.
Prepare one runnable query
Choose a disposable SQLite learning environment and test it before class. Learners need to run a WITH ... SELECT statement and see its result. Use only the synthetic values below; this lesson needs no application database or private records.
SQLite's WITH documentation explains ordinary common table expressions as statement-scoped views. Here, the attempts CTE supplies named columns and six rows without creating a stored table. Give learners the full statement, including the final semicolon:
WITH attempts(topic, outcome, minutes) AS (
VALUES
('joins', 'complete', 10),
('joins', 'retry', 20),
('joins', 'complete', 30),
('filters', 'complete', 15),
('filters', 'retry', 25),
('sorting', 'complete', 12)
)
SELECT
topic,
COUNT(*) AS attempt_count,
AVG(minutes) AS average_minutes
FROM attempts
GROUP BY topic
ORDER BY topic;
These are fictional attempts, not measured student results. Each row represents an attempt, not necessarily a different person. A larger count or average does not establish learning, difficulty, effort, or teaching quality.
For a suggested forty-minute lesson, allow five minutes for access and column meanings, eight for predictions, ten for running and explaining the query, twelve for filtering changes, and five for an independent explanation. Adjust the pace to the group's SQL experience.
Predict the groups before pressing Run
Ask learners to circle the repeated topic labels or list the row numbers under each topic. They should identify three groups: filters has two rows, joins has three, and sorting has one.
Next, ask for the output column names and the meaning of one output row. The SQLite SELECT reference describes assigning rows with equal grouping-expression values to the same group. In this query, topic chooses the grouping key; the aggregate expressions summarize each group.
Have everyone write a prediction before showing a shared result. Someone who predicts six output rows may be treating the query as a row-by-row calculation. Ask them which original minutes value would belong in the joins summary, where three different values are present.

The illustrations show the idea of grouping. Their card counts and symbols are not a diagram of the exact six-row dataset.
Explain the counts and averages
Run the full statement. Read each result as topic, attempt_count, average_minutes:
filters:2,20.0, because the minutes are 15 and 25.joins:3,20.0, because 10, 20, and 30 total 60, divided by three.sorting:1,12.0, because its group contains only the 12-minute row.
Your editor may display whole-number averages without the decimal; check the numeric value and column meaning. ORDER BY topic makes the requested output order explicit. Do not teach learners to depend on grouping alone to sort results.
SQLite's aggregate-function documentation distinguishes COUNT(*), which counts rows, from COUNT(column), which counts non-NULL values. AVG(minutes) averages the non-NULL inputs. Every minutes value in the starting dataset is present.
As an optional check, replace the joins value 30 with NULL. Predict COUNT(*) = 3, COUNT(minutes) = 2, and AVG(minutes) = 15.0 for joins. Restore 30 before the next section. Explain that a missing value is different from a recorded zero.
Compare filtering rows with filtering groups
First ask: “What if we summarize only complete attempts?” Keep the original WITH attempts ... block and replace its entire SELECT portion with this tail:
SELECT
topic,
COUNT(*) AS attempt_count,
AVG(minutes) AS average_minutes
FROM attempts
WHERE outcome = 'complete'
GROUP BY topic
ORDER BY topic;
The result is filters with one attempt averaging 15.0 minutes, joins with two averaging 20.0, and sorting with one averaging 12.0. Ask learners to cross out the two retry rows before calculating. WHERE filters input rows before grouping.
Now return to all six original rows. Replace the SELECT tail again, removing that WHERE condition:
SELECT
topic,
COUNT(*) AS attempt_count,
AVG(minutes) AS average_minutes
FROM attempts
GROUP BY topic
HAVING COUNT(*) >= 2
ORDER BY topic;
This keeps filters and joins with their original counts and averages; sorting is removed. HAVING filters groups after grouping. Keep those two questions separate: which individual attempts qualify, and which resulting groups qualify?
For a final prediction, combine the complete-only WHERE with this HAVING. Only joins remains, with two attempts averaging 20.0 minutes. Let learners explain why filters disappears even though it had two rows in the unfiltered dataset.

Check reasoning as well as syntax
Collect a query, its result, and one sentence explaining the population being summarized. A useful answer names whether it includes all attempts or only complete ones, then explains the group condition.
Keep selected non-aggregate columns aligned with the grouping key in this exercise. Adding outcome to the output without grouping or aggregating it does not give a reliable label for a mixed-outcome topic. SQLite permits bare columns in aggregate queries, so an accepted statement alone is not proof that the result means what a learner intended.
If a result differs, check the dataset first, then whether a previous filter was left in place. Ask learners to change one condition and predict the effect before rerunning. This makes an incorrect prediction something you can discuss concretely.
Prepare the browser route for the lesson
Test the SQL sandbox, documentation, and sign-in hosts before starting a Focus Room session. Use the website-rule setup guide and participant joining guide to prepare access.
Focus Room applies configured hostname and subdomain rules in the participating browser while its extension session is active. It does not limit an allowed website to one saved query. It does not run SQL, inspect results, grade explanations, supply a database, or control other browsers, terminals, desktop editors, or devices.
End by asking each learner to explain one filtered result without copying the instructor's explanation. You can create a Focus Room account to organise the browser work period around that practice.

