Typorithm · medium
Step 1 of 4 · intro card
Make the numerator, denominator and rounding policy visible in a share calculation.
A percentage is meaningful only when its numerator and denominator describe compatible populations. The worked query counts active roles by country, then divides each country count by the total active count across those groups. The denominator is six roles, not the complete stored population eight.
The resulting shares are approximately 16.67 percent for CA and IL and 33.33 percent for UA and US. These are fictional fixture shares, not market estimates. A real report also needs source coverage, geography and time-window context.
The counts intermediate result has one row per represented country.
The counts intermediate result has one row per represented country. SUM(n) OVER () attaches the complete grouped total without collapsing those country rows. Multiplying by 100.0 requests non-integer arithmetic, and ROUND chooses two display decimals. NULLIF guards a zero denominator where one could be present.
The decimal marker matters because integer arithmetic can truncate a fraction before rounding. Exact output types depend on the engine. PostgreSQL SUM over bigint counts produces numeric, so an integer-looking expression does not by itself prove integer division. The lab compares normalized numeric values while retaining actual SQL execution in both SQLite and PostgreSQL.
sql
WITH counts AS (SELECT country,COUNT(*) AS n FROM roles WHERE active=1 GROUP BY country)
SELECT country,n,ROUND(100.0*n/NULLIF(SUM(n) OVER (),0),2) AS share
FROM counts ORDER BY country;The counterexample uses integer 100 and explicitly casts the total to an integer, producing truncated country shares in both teaching engines.
The counterexample uses integer 100 and explicitly casts the total to an integer, producing truncated country shares in both teaching engines. Rounding afterward cannot recover fractional information already lost. A different common error filters the displayed countries before computing a denominator that was supposed to represent the whole population.
Rounding can also make displayed shares fail to sum to exactly one hundred on other inputs. Do not silently adjust a category without declaring a reconciliation policy. Keep unrounded measures for further arithmetic and round only at a justified presentation boundary.
sql
WITH counts AS (SELECT country,COUNT(*) AS n FROM roles WHERE active=1 GROUP BY country)
SELECT country,n,ROUND(100*n/CAST(NULLIF(SUM(n) OVER (),0) AS INTEGER),2) AS share
FROM counts ORDER BY country;Compute shares only within the active UA-or-US population.
Compute shares only within the active UA-or-US population. Predict two countries with two roles each and a fifty-percent share each. Apply that population filter before grouping and make clear that the denominator is now four, not the global active total six.
This is an intentionally different report, not a way to make the original global shares larger. State the revised denominator in the label. The task changes one eligibility condition while reusing the numeric-division and window-total mechanisms.
Step 1: Restrict the active source population to UA or US. Step 2: Compute the total from those same grouped counts.
sql
-- Reuse the share calculation over a UA-or-US active population.
-- Label its denominator as the selected subset, not the global market.The solution returns UA 2 50 and US 2 50.
The solution returns UA 2 50 and US 2 50. Its exact acceptance table checks both the counts and the shares, preventing a query from accidentally computing fifty percent from the wrong underlying records. The zero-denominator guard remains even though this fixture has positive counts.
An empty grouped population produces no country rows here. A report that must retain zero-count countries would need a separate country scaffold and a deliberate zero-total policy. Do not imply that NULLIF alone creates missing output categories.
sql
WITH counts AS (SELECT country,COUNT(*) AS n FROM roles
WHERE active=1 AND country IN ('UA','US') GROUP BY country)
SELECT country,n,ROUND(100.0*n/NULLIF(SUM(n) OVER (),0),2) AS share
FROM counts ORDER BY country;In an interview, explain the measured population and denominator before the formula.
In an interview, explain the measured population and denominator before the formula. Ask whether a filtered view should show share of the selected subset or share of the complete eligible market. Both can be useful, but they are different questions.
The next lesson builds sequence groups after deduplicating to a calendar-day grain. As with percentages, correctness depends on the population and intermediate grain before a compact window formula is applied.
Optional stretch — after the core task works: Construct three equal groups and explain why rounded displayed shares can total 99.99 rather than 100.
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.
Make the numerator, denominator and rounding policy visible in a share calculation.