1 · Reporting patterns and edge cases · 25 MIN

Calculate category shares with an explicit denominator

First aggregate each category, then divide its total by the total across categories. Multiplying by 1.0 requests fractional division in SQLite rather than integer truncation. NULLIF prevents division by zero, and COALESCE gives an empty category total a deliberate numeric identity. Rounding belongs at the final presentation stage because rounding every row first can distort a total. The fixed dataset sums to 20, making the expected shares easy to check.

Define the output row grain and ordering before choosing SQL operators.

Read the example

WITH totals AS (SELECT category, COALESCE(SUM(amount),0) AS total FROM events GROUP BY category) SELECT category, total, ROUND(1.0 * total / NULLIF(SUM(total) OVER (),0),2) FROM totals ORDER BY category;

Check the expected output

[["food",17,0.85],["travel",3,0.15]]

Your challenge

Return category, total amount and its share of the overall amount rounded to two decimals, ordered by category. Return NULL share if the overall total is zero.

Solution cost: Aggregation scans input; window ordering can add O(n log n) sorting. Inspect the database plan. time · Grouping or window buffers depend on the plan and partition size. space

Common trap

Integer division can silently turn a valid fractional share into zero.

Open interactive exercise →See the syllabus

Further reading: SQLite window functions