Normalization Tradeoffs in Practice

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Visa

Problem

The finance team needs total revenue per product category, where a transaction's revenue is the quantity sold times the product's listed price. Return each category with its revenue.

Clean data or fast queries? You can't always have both.

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

The column that lies

This is a derived-measure aggregation dressed up as a finance report. The skill being probed: can you build revenue per row as t.quantity * p.price, a value that only exists after the join, and then sum it by category? Everyone writes the JOIN and the GROUP BY. What separates candidates is ignoring total_amount. It sits right there on transactions looking like the answer, but the spec defines revenue as quantity times the listed price. Sum total_amount instead and every category comes back wrong while still looking plausible.

Check the first sample row. Transaction 1067 bought 2 units of product 1001 at a listed 17.52, which is 35.04 of revenue. Its total_amount says 23.46. That gap repeats row after row, so no category total survives.

Trusting the stored total

SUM(t.total_amount) grouped by p.category. You still need the join to get category, so this saves nothing. It sums whatever the checkout recorded, discounts and old prices included, and misses the spec on every row.

Recomputing from the definition

SUM(t.quantity * p.price) grouped by p.category. The join already brings in price, so the multiplication costs nothing extra. Each row is priced by the definition the finance team gave.

Building it

Step 1: Join transactions to products on product_id

Both price and category live only on products, so no correct version skips the join. Use a plain inner JOIN. A transaction whose product_id has no catalog row has no price and no category, so it belongs in no total.

Step 2: Price each row before aggregating

Compute t.quantity * p.price inside the SUM, so each transaction's revenue is formed on its own row. The multiplication has to happen per row, after the join and before the sum.

Step 3: Group and order by p.category

GROUP BY p.category collapses the priced rows into one total per category, and ORDER BY p.category walks them alphabetically, as the prompt asks.

SELECT p.category, SUM(t.quantity * p.price) AS total_revenue
FROM transactions t
JOIN products p ON t.product_id = p.product_id
GROUP BY p.category

Common follow-up questions

• Finance now wants realized revenue next to list-price revenue. How do you return both in one pass? (Two `SUM`s over the same join, plus a discount column derived from them.)
• How would you report how much revenue the inner join dropped for orphan `product_id` values? (Anti-join or `LEFT JOIN` with `p.product_id IS NULL`, kept separate from the category totals.)
• Electronics comes back as 8482.800000000001. What do you change? (Floating-point `REAL` money; `ROUND` for display versus a `DECIMAL` type in storage.)
• Prices change over time. How would you compute revenue at the price in effect on `transaction_date`? (A price-history table and a range join, which is the normalized alternative to `total_amount`.)