Typorithm · medium
Step 1 of 4 · intro card
Combine eligibility, tie policy, ranking and empty-company retention in a reviewable report.
The final report retains every company and selects one active representative role by highest known salary, placing unknown salaries after known observations and breaking ties by identity. This is a deterministic teaching selection policy, not a claim that salary measures role quality.
The expected selected identities are A 1, B 4 and C 6, plus D with no role. A known zero remains a known observation. A company with only unknown active salaries would still receive a deterministic representative under the explicit missing-value ordering.
The ranked stage filters active roles and assigns ROW_NUMBER per company.
The ranked stage filters active roles and assigns ROW_NUMBER per company. A CASE expression places unknown salaries last, salary descending chooses the highest observed value and id resolves remaining ties. The final LEFT JOIN matches only rank one while retaining each company.
The rank condition belongs in ON because the report requires empty company profiles too. The final output grain is one company. Prove that the ranked stage supplies at most one matching role per company before attaching any additional measures.
sql
WITH ranked AS (SELECT id,company_id,salary,ROW_NUMBER() OVER
(PARTITION BY company_id ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END,salary DESC,id) AS rn
FROM roles WHERE active=1)
SELECT c.id,r.id AS role_id,r.salary FROM companies c
LEFT JOIN ranked r ON r.company_id=c.id AND r.rn=1 ORDER BY c.id;The counterexample uses an inner join for rank-one matches.
The counterexample uses an inner join for rank-one matches. It returns plausible winners for hiring companies but loses D. Another easy error ranks all roles and filters active status afterward, potentially selecting an archived winner and then discarding the company's valid active alternatives.
Test an empty company, an unknown salary, a known zero and a tie policy. A dataset where every company has one complete active role cannot reveal these boundary mistakes. Keep eligibility before ranking and final retention after ranking as separate requirements.
sql
WITH ranked AS (SELECT id,company_id,salary,ROW_NUMBER() OVER
(PARTITION BY company_id ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END,salary DESC,id) AS rn
FROM roles WHERE active=1)
SELECT c.id,r.id AS role_id,r.salary FROM companies c
JOIN ranked r ON r.company_id=c.id AND r.rn=1 ORDER BY c.id;Change the representative policy to newest active publication, resolving date ties by descending identity.
Change the representative policy to newest active publication, resolving date ties by descending identity. Keep every company in the output. Predict A role 2, B role 8, C role 6 and D with no match. Salary remains an output observation but no longer drives selection.
This task changes the window ordering while preserving active eligibility and the retaining join. Do not leave the old salary order ahead of publication. Work through the two newest A candidates and their equal date before checking the full query.
Step 1: Keep the ranked active population. Step 2: Order by publication and identity descending, then retain every company.
sql
-- Keep active eligibility and the retaining company join.
-- Rank by newest publication, then highest identity for a date tie.The solution returns A 2 with salary 80, B 8 with zero, C 6 with 120 and D with null role and salary.
The solution returns A 2 with salary 80, B 8 with zero, C 6 with 120 and D with null role and salary. That is a different representative policy with the same company-grain output contract. Neither result should be labelled best without a product-defined relevance model.
Acceptance compares company identity, chosen role identity and salary observation. Checking only the four-company count would miss wrong winners, while checking only hiring winners would miss the empty-company regression. Both sides of the contract need independent expected values.
sql
WITH ranked AS (SELECT id,company_id,salary,ROW_NUMBER() OVER
(PARTITION BY company_id ORDER BY published DESC,id DESC) AS rn
FROM roles WHERE active=1)
SELECT c.id,r.id AS role_id,r.salary FROM companies c
LEFT JOIN ranked r ON r.company_id=c.id AND r.rn=1 ORDER BY c.id;Explain the complete pipeline as eligibility, deterministic selection and retained entity output.
Explain the complete pipeline as eligibility, deterministic selection and retained entity output. Then discuss missing-value policy and what a future one-to-many evidence join would do to the final grain. This is a practical interview review that combines many earlier lessons without requiring a memorized giant query.
Use the companion lab to run the worked and solution queries in both engines, inspect the intentionally wrong result and attempt the starter before revealing the solution. Reading and self-check progress are available in the app; SQL execution remains in the disposable local lab.
Optional stretch — after the core task works: Add a company with only unknown active salaries and verify the declared representative and null observation policy.
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 eligibility, tie policy, ranking and empty-company retention in a reviewable report.