Capstone: a country-aware hiring report

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Capstone: a country-aware hiring report

Concept and contract

Combine filters, skill evidence, correct grain and observation coverage without losing empty companies.

The report contract is specific: retain every company, count active roles in UA or US published during the four-day teaching interval, require SQL skill evidence and show known-salary coverage plus the observed average. Vacancy country comes from roles, not company headquarters or unrelated articles.

For the fixture, eligible role identities are 1, 2 and 3. A has two, B has one, and C and D have zero. B's eligible role has unknown salary, so its observed average must remain null rather than being displayed as a measured zero.

Mechanism and worked example

Build an eligible intermediate result first.

Build an eligible intermediate result first. EXISTS asks whether at least one SQL skill row is present without multiplying a role by the number of its skills. Aggregate eligible roles by company_id, then left join those company-grain measures to all companies.

COALESCE is justified for absent role and observed-value counts. It is not applied to mean salary because no observed mean is a different fact from a known zero mean. ROUND controls the displayed precision only after the eligible observed values are selected.

Complete worked example

sql

WITH eligible AS (SELECT r.* FROM roles r WHERE r.active=1
 AND r.country IN ('UA','US')
 AND r.published >= '2026-10-01' AND r.published < '2026-10-05'
 AND EXISTS (SELECT 1 FROM role_skills s WHERE s.role_id=r.id AND s.skill='SQL')),
 counts AS (SELECT company_id, COUNT(*) AS n, COUNT(salary) AS observed,
 ROUND(AVG(salary),2) AS mean FROM eligible GROUP BY company_id)
SELECT c.id, COALESCE(x.n,0), COALESCE(x.observed,0), x.mean
FROM companies c LEFT JOIN counts x ON x.company_id=c.id ORDER BY c.id;

Reasoning: Eligibility is proved before grouping; EXISTS avoids skill multiplication.

Counterexamples and common mistakes

The counterexample joins every skill row directly and counts the multiplied combinations.

The counterexample joins every skill row directly and counts the multiplied combinations. Role 1 has two skill rows, so its contribution is inflated. It also fails to require the requested skill, admitting unrelated records. A plausible grouped table can therefore conceal several contract violations.

Review population and grain independently. First prove eligible identities, then prove each contributes once, then reconcile grouped measures. Adding DISTINCT to every measure is not an automatic repair, especially for averages and sums where equal values can belong to separate legitimate roles.

Counterexample: a plausible but wrong query

sql

SELECT c.id, COUNT(r.id), COUNT(r.salary), ROUND(AVG(r.salary),2)
FROM companies c LEFT JOIN roles r ON r.company_id=c.id
AND r.active=1 AND r.country IN ('UA','US')
LEFT JOIN role_skills s ON s.role_id=r.id GROUP BY c.id ORDER BY c.id;

Reasoning: A raw skill join changes both eligibility and contribution count.

Independent practice: small query

Adapt the complete report to active Israeli roles with SQL skill evidence during the same interval.

Adapt the complete report to active Israeli roles with SQL skill evidence during the same interval. Keep all companies in the output and preserve unknown-value policy. Predict only company C with one eligible and observed role, mean 120; every other company has zero counts and a null mean.

Make the country change in the eligible stage rather than filtering the final company rows. Reuse the grouped measures and left join. This small independent change tests whether you understand the pipeline boundaries rather than requiring a new report from scratch.

Step 1: Check the Israeli eligible identity first. Step 2: Reuse company-grain counts and the retaining join.

Practice starter: solve before reading the solution

sql

-- Copy the worked pipeline and change only eligible country to IL.
-- Keep all companies and preserve the count/mean missingness policy.

Reasoning: Change only the eligible country while keeping every company.

Hint 1: The only eligible identity is 6.
Hint 2: Do not coalesce the absent average to zero.

Solution, tests and trade-offs

The solution retains A, B, C and D.

The solution retains A, B, C and D. C has counts one and one with mean 120; the other companies have zero counts and no observed mean. This verifies both the filtered vacancy population and the all-company output population.

The acceptance table must compare each company's three measures, not merely the total number of rows. If the product later requests only hiring companies, add that as an explicit final requirement. Do not silently remove empty profiles because the current example happens to focus on hiring.

Practice solution and acceptance checks

sql

WITH eligible AS (SELECT r.* FROM roles r WHERE r.active=1 AND r.country='IL'
 AND r.published >= '2026-10-01' AND r.published < '2026-10-05'
 AND EXISTS (SELECT 1 FROM role_skills s WHERE s.role_id=r.id AND s.skill='SQL')),
 counts AS (SELECT company_id, COUNT(*) AS n, COUNT(salary) AS observed,
 ROUND(AVG(salary),2) AS mean FROM eligible GROUP BY company_id)
SELECT c.id, COALESCE(x.n,0), COALESCE(x.observed,0), x.mean
FROM companies c LEFT JOIN counts x ON x.company_id=c.id ORDER BY c.id;

Reasoning: Only C has an eligible Israeli SQL role; absent means remain null.

Interview reasoning and next step

Present the capstone as a sequence of evidence: eligibility, identity preservation, company aggregation, empty-company retention and observed denominator.

Present the capstone as a sequence of evidence: eligibility, identity preservation, company aggregation, empty-company retention and observed denominator. Explain what each output field means and where its population is defined. These are interview skills and everyday report-review skills, not just syntax exercises.

The analytics track extends this foundation with anti-joins, reusable pipelines, rankings, frames, changes over time and execution plans. Attempt the report before reading its solution. The local lab executes both accepted queries against disposable SQLite and PostgreSQL fixtures.

Optional stretch — after the core task works: Add an SQL skill duplicate under a deliberately relaxed child constraint and prove the EXISTS population is unchanged.

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.

Combine filters, skill evidence, correct grain and observation coverage without losing empty companies.