The Weight of Things

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

mid

Asked in

Visa

Problem

Some of this year's transactions point at a product_id that has no row in products, so that spend has no category and counts toward neither a category's revenue nor the total it is measured against. An analyst tracking where 2026 spend concentrates wants each category's revenue and its share of that categorized total as an unrounded fraction between 0 and 1, biggest categories first.

Every category claims part of the year. Find where it gathers.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

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 grains in one result: the numerator is per-category revenue, the denominator is the grand total of exactly those categories. Anyone can write the join and the GROUP BY. What separates candidates is where the denominator comes from. The reflex is a scalar subquery over transactions, and that total runs over a different population: it drops the year filter, or it keeps spend whose product_id never matched a category. Either way the shares stop summing to 1, and merchandising misreads how concentrated this year's spend really is, silently, because the query still returns plausible 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. Put it in WHERE so it runs before the aggregation, and so the numerator and the denominator both see only this year's rows.

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

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

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.)