Above Category Average

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

Domain

SQL

Difficulty

easy

Seniority

L5

Problem

Every row in transactions is one sale of a product listed in products, and each product belongs to exactly one category. Find the products whose average sale amount beats the average over every sale in their category, with both averages shown side by side.

The category average is one thing. These beat it.

Tables

products

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

transactions

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

Expected output

Worked solution and explanation

What the problem really is

This is a two-grain comparison dressed up as a merchandising report: every product is judged against a bar computed at a coarser grain over the same sales. Anyone can average one product's sales. What separates candidates is where the category bar comes from. It has to pool every sale in the category, not average the product averages already in hand. Reuse the product-level numbers and a product with one sale counts as much as a product with three, the bar moves, and products land on the wrong side of it.

Break down the requirements

Step 1: Compute each product's mean transaction amount

First CTE: join products to transactions on product_id, GROUP BY product_id, product_name, category, and compute AVG(total_amount). Products with no sales never appear, and sales whose product_id is NULL fall out of the join.

Step 2: Compute each category's pooled mean

Second CTE: join the same two tables, GROUP BY category, and compute AVG(total_amount). That is SUM(total_amount) / COUNT(*) across every sale in the category, so a product weighs in by how many times it sold. It is not an average of per-product averages.

Step 3: Join the two CTEs on category

Each product maps to exactly one category, so each product row picks up exactly one category average. The result stays at product grain with the pooled bar attached.

Step 4: Keep strictly-above products

WHERE product_avg > cat_avg. Strictly greater, because matching the category is not beating it. Return product_name, product_avg, and cat_avg renamed to category_avg.

The solution

WITH product_avg AS (
    SELECT p.product_id,
           p.product_name,
           p.category,
           AVG(t.total_amount) AS product_avg
    FROM products p
    JOIN transactions t ON t.product_id = p.product_id
    GROUP BY p.product_id, p.product_name, p.category
),
category_avg AS (
    SELECT p.category,
           AVG(t.total_amount) AS cat_avg
    FROM products p
    JOIN transactions t ON t.product_id = p.product_id
    GROUP BY p.category
)
SELECT pa.product_name,
       pa.product_avg,
       ca.cat_avg AS category_avg
FROM product_avg pa
JOIN category_avg ca ON ca.category = pa.category
WHERE pa.product_avg > ca.cat_avg

Common follow-up questions

• Rewrite this in a single pass using a window function. (Group by product with `SUM(total_amount)` and `COUNT(*)`, then compute `SUM(SUM(total_amount)) OVER (PARTITION BY category) * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY category)` as the pooled bar and filter where the product average is greater. This reads `transactions` once.)
• How would the answer change if "category average" meant an average of per-product averages? (Aggregate to products first, then average those. Every product weighs the same, so the bar drifts toward low-volume products.)
• How would you include products with no transactions, showing NULL for their average? (`LEFT JOIN transactions` in the per-product CTE. Their `product_avg` is NULL, which fails the strict `>` and drops out of this filter, so list them in a separate output if they must be shown.)
• How would you sort products by how far above their category average they sit? (Add `(product_avg - cat_avg) AS lift` and `ORDER BY lift DESC`, or report `(product_avg / cat_avg - 1)` as a percentage lift.)