Typorithm · medium
Step 1 of 4 · intro card
Name intermediate grains while keeping logical meaning separate from materialization assumptions.
A common table expression gives a query result a local name. The worked pipeline first names active roles, then counts them by company and finally keeps companies with at least two active roles. The two named stages make population and aggregation boundaries visible.
The expected result is A with two and B with three. Names such as eligible and counts communicate each stage's meaning better than a single dense expression. They do not create a durable table, grant permissions or establish a stored cache.
Each CTE has its own output schema and grain.
Each CTE has its own output schema and grain. Eligible remains one row per role; counts becomes one row per company_id. The final query filters counts using an ordinary WHERE because n is now an output column of the completed grouped stage.
That does not contradict HAVING in the earlier lesson. HAVING filters inside the grouping query; a later query can filter its already-computed output. Keep logical stages distinct from physical execution: PostgreSQL and SQLite may inline or materialize forms according to their own rules.
sql
WITH eligible AS (SELECT * FROM roles WHERE active=1),
counts AS (SELECT company_id, COUNT(*) AS n FROM eligible GROUP BY company_id)
SELECT company_id, n FROM counts WHERE n>=2 ORDER BY company_id;The counterexample counts every stored role rather than the eligible active population.
The counterexample counts every stored role rather than the eligible active population. It reports A 3, B 3 and C 2, all plausible-looking company counts but wrong for the requested status. Renaming the intermediate result does not validate its content.
A CTE is not an automatic performance optimization. It can improve reviewability while a physical plan still needs measurement. Avoid assuming every named stage is a temporary table or every reference is recomputed; consult the actual engine version and execution plan.
sql
WITH counts AS (SELECT company_id, COUNT(*) AS n FROM roles GROUP BY company_id)
SELECT company_id, n FROM counts WHERE n>=2 ORDER BY company_id;Adapt the eligible stage to roles with known salary, regardless of active status, then return companies with at least two such observations.
Adapt the eligible stage to roles with known salary, regardless of active status, then return companies with at least two such observations. Predict A with three, B with two and C with two. Keep the grouped and final stages unchanged.
The task replaces one population rule rather than adding it. Do not retain active=1 silently. Inspect eligible identities before grouping so a correct-looking final threshold does not hide the wrong source population.
Step 1: Choose the new eligible population. Step 2: Keep the grouped measure and final threshold intact.
sql
WITH eligible AS (SELECT * FROM roles WHERE /* known salary */),
counts AS (SELECT company_id, COUNT(*) AS n FROM eligible GROUP BY company_id)
SELECT company_id,n FROM counts WHERE n>=2 ORDER BY company_id;The solution uses salary IS NOT NULL and counts its role rows by company.
The solution uses salary IS NOT NULL and counts its role rows by company. A contributes 1, 2 and 7; B contributes 4 and 8; C contributes 5 and 6. The genuine zero observation is included, while missing role 3 is not.
The exact acceptance table verifies the changed population and the retained threshold. Explain why COUNT(*) is correct inside this already-filtered eligible stage even though COUNT(salary) would be needed to measure coverage over an unfiltered population.
sql
WITH eligible AS (SELECT * FROM roles WHERE salary IS NOT NULL),
counts AS (SELECT company_id, COUNT(*) AS n FROM eligible GROUP BY company_id)
SELECT company_id,n FROM counts WHERE n>=2 ORDER BY company_id;In an interview, use names to expose assumptions and grain changes.
In an interview, use names to expose assumptions and grain changes. Show a small intermediate result instead of claiming a long CTE chain is self-evidently clean. Every stage should have a purpose that helps the reader assess eligibility, cardinality or an independent measure.
The next lessons retain row identities while computing group-aware rankings and summaries. Those window operations differ from GROUP BY: the distinction between source grain and output grain remains essential even when the syntax becomes more compact.
Optional stretch — after the core task works: Compare the CTE and equivalent nested-query plans without assuming either is automatically faster.
Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.
Name intermediate grains while keeping logical meaning separate from materialization assumptions.