Product Ratings vs Sales

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

L3

Problem

Do product categories that sell higher-rated items actually pull more revenue? An analyst wants each category's average rating across its sales in transactions, every sale counting at the rating of its product in products, next to the category's total revenue. Products that were never rated stay out of both figures.

Do higher ratings actually mean more revenue?

Tables

products

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

transactions

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

Expected output

Worked solution and explanation

One filter, two aggregates

This is a scoping problem dressed up as a ratings dashboard. Revenue lives only in transactions, so the join is forced, and once you join, AVG(p.rating) runs over sale rows, which is exactly the per-sale average the question asks for. The real trap is the null filter. AVG already skips a NULL rating, so WHERE p.rating IS NOT NULL looks redundant and gets deleted. Do that and avg_rating comes out identical, the spot check passes, and every category holding an unrated product that sold ships an inflated total_revenue, because SUM(t.total_amount) never looks at the rating.

Build it in three moves

Step 1: Join transactions to products on product_id

Inner is the right join. A sale whose product_id is NULL has no category to land in, and a category with no sales has no revenue to report. After the join, each row is one sale wearing its product's rating and category.

Step 2: Drop unrated products in WHERE

Filter on p.rating IS NOT NULL before grouping. The filter is not there for the average, which skips NULLs on its own. It is there so the sales of unrated products never reach SUM(t.total_amount).

Step 3: Group by category and compute both measures

AVG(p.rating) over the joined rows weights each product by how often it sold, so a product sold three times votes three times. SUM(t.total_amount) adds the revenue as recorded, refunds and voided sales included.

SELECT
    p.category,
    AVG(p.rating) AS avg_rating,
    SUM(t.total_amount) AS total_revenue
FROM products p
JOIN transactions t ON p.product_id = t.product_id
WHERE p.rating IS NOT NULL
GROUP BY p.category

Filter dropped

avg_rating matches the correct answer to the last digit, because AVG skips NULL ratings. total_revenue grows in every category where an unrated product sold.

Filter kept

Both metrics describe the same population: sales of rated products. The average and the revenue now answer one question about one set of rows.

Common follow-up questions

• What does the query return if every `rating` is NULL? (An empty result: the `WHERE` empties the input before grouping, so no category row survives.)
• How would you report the per-product average instead, one vote per product? (Tests rolling `transactions` up per `product_id` before the join, so the fan-out never reaches the average.)
• How would you keep categories with no sales, showing revenue 0? (Swap to a left join from `products` and default the sum with `COALESCE`.)
• At 10x the transactions, how do you keep the join from stalling on hot `product_id` values? (Probes broadcasting the small side versus salting a skewed key.)