Device Mix as Row Percentages — Solution & ExplanationProblems / Device Mix as Row Percentages / EditorialApproach

One GROUP BY, two roles. Group sessions by country. Within each group, COUNT(*) is the row total (denominator) and each device's conditional count is a numerator — no self-join or second query needed.

Conditional counts. COUNT(*) FILTER (WHERE device = 'desktop') (DuckDB) or SUM(CASE WHEN device = 'desktop' THEN 1 ELSE 0 END) (portable) count only the matching rows of the group. A device absent from a country simply counts 0, so the zero-tablet country produces 0.00 naturally — no COALESCE required.

Force float math. 100.0 * cnt / COUNT(*) — the 100.0 literal promotes the whole expression to floating point. 100 * cnt / COUNT(*) with integer division would truncate every percentage below 100% straight to 0.

Round to 2 decimals and order by country.

The PySpark version is the same shape: one .agg() with three F.round(100.0 * F.sum(F.when(...)) / F.count("*"), 2) expressions.Key InsightRow-percentage pivots hinge on the fact that an aggregate query can compute the numerator and denominator in the same pass: FILTER/CASE restricts which rows feed one aggregate while COUNT(*) still sees the whole group. The two classic failure modes are both silent — integer division truncating every share to zero, and treating a missing category as NULL instead of an empty count. Conditional counting sidesteps the second automatically: counting zero matching rows is 0, and 100.0 * 0 / total is a clean 0.00.Hints

This is a pivot where the cell value is a percentage of the ROW total. Both ingredients come from the same GROUP BY country: COUNT(*) is the denominator, and a conditional count per device is each numerator.

A conditional count is COUNT(*) FILTER (WHERE device = 'desktop') in DuckDB, or the portable SUM(CASE WHEN device = 'desktop' THEN 1 ELSE 0 END). Either way, a device with no sessions in that country counts 0 — which is why the zero-tablet country comes out 0.00 instead of NULL, with no special handling.

Write 100.0 * count / total, not 100 * count / total: with integer operands the division truncates to 0 before the multiplication can save it. Then ROUND(..., 2).

In PySpark, F.sum(F.when(F.col('device') == d, 1).otherwise(0)) / F.count('*') inside one .agg() per device column, wrapped in F.round(..., 2).

Solve Device Mix as Row Percentages yourself →