# The Weight of Things > Every category claims part of the year. Find where it gathers. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## Problem We run a card-payments platform, and merchandising wants to see where this year's spend is concentrating across product categories. For each category, show its 2026 revenue and that revenue as a plain fraction of the year's total spend (a value between 0 and 1, not rounded), biggest categories first. ## Worked solution and explanation ### What this problem is really testing Underneath the merchandising story this is a part-to-whole ratio that lives at two different grains in the same result: the numerator is per-category revenue (one row per category), but the denominator is the grand total across every category (a single number). Anyone can write the join and the GROUP BY. The real question is how you reach the grand total without re-scanning the transactions table or, worse, computing an average per group and calling it a share. Get the denominator grain wrong and every percentage is off, silently, because the query still runs and still returns plausible-looking numbers. --- ### Break down the requirements #### Step 1: Join spend to its category transactions carries the money in total_amount but not the category; products carries category. Inner-join on product_id so every dollar lands under a category. An inner join is correct here: a transaction with no matching product has no category to attribute to. #### Step 2: Scope to the year strftime('%Y', t.transaction_date) = '2026' keeps only this year's spend. Do this in WHERE so the filter runs before the aggregation, not after. #### Step 3: Sum per category, then divide by the whole GROUP BY category gives SUM(total_amount) per category. The trick is the denominator: SUM(SUM(t.total_amount)) OVER () sums the per-category totals across the entire result set in the same pass. The inner SUM aggregates within each group; the outer windowed SUM adds those group totals together. Multiply the numerator by 1.0 to force real division instead of integer truncation, and leave the ratio unrounded so it stays an exact fraction of the whole. --- ### The solution **Per-category revenue and its share of the year** ```sql SELECT p.category AS category, SUM(t.total_amount) AS category_revenue, SUM(t.total_amount) * 1.0 / SUM(SUM(t.total_amount)) OVER () AS revenue_share FROM transactions t JOIN products p ON t.product_id = p.product_id WHERE strftime('%Y', t.transaction_date) = '2026' GROUP BY p.category ORDER BY category_revenue DESC ``` > **The nested aggregate window** > > SUM(SUM(x)) OVER () reads strangely the first time. The inner SUM is the normal group aggregate; the outer SUM is a window function running over the already-grouped rows, with an empty OVER () meaning 'the whole result'. It computes both grains in a single pass, so you never scan transactions twice and never need a self-join or a correlated subquery for the total. > **Common Pitfall** > > Reaching for AVG(total_amount) or dividing by COUNT(*) to get a 'share' produces a per-row average, not a fraction of the total, and the numbers will not sum to 1. Two more classic misses: integer division (without the 1.0, SUM/SUM truncates every share to 0 in engines that treat the operands as integers), and scaling the ratio to a rounded percentage when the ask is a plain unrounded fraction between 0 and 1. > **Cost Analysis** > > At 400M transactions and ~15 GB, the join hash-builds on the small products dimension (thousands of rows) and streams the fact table once. The windowed grand total is computed over the handful of grouped category rows, essentially free. Partition pruning on transaction_date keeps the scan to a single year's partitions rather than the full history. > **Interviewers Watch For** > > Whether you compute the grand total in one pass instead of a second scan, whether you guard against integer division, and whether you can articulate why the denominator is a different grain than the numerator. Bonus points for asking whether refunds (negative total_amount) should net out or be excluded before the shares are computed. --- ## Common follow-up questions - Rewrite the share using a scalar subquery for the grand total instead of a window function. When would you prefer each? _(Tests whether they understand the window aggregate avoids a second scan and reason about optimizer materialization of the subquery.)_ - Now report each category's share within its own region rather than across all spend. What changes? _(Pushes the empty OVER () to OVER (PARTITION BY region), showing they understand the window frame defines the denominator's grain.)_ - If total_amount can be negative for refunds, how does that affect the shares and how would you handle it? _(Surfaces whether they net refunds, exclude them, or treat them as a separate signal, and whether a category could end up with a negative share.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_weight_of_things) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.