Top Average By Region

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

Asked in

Sanofi

Problem

Pull the three product categories with the highest average transaction amount, using data from both the transactions and products tables. Return each category and its average, from highest to lowest.

Region by region, who pulls the best average?

Tables

transactions

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

products

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

Expected output

Worked solution and explanation

What this really is

This is a weighted average across a join, dressed up as a category leaderboard. The category lives only in products and the money only in transactions, so the join is the easy part. What separates candidates is the grain the average is taken at. Average the joined sale rows and every sale gets one vote. Average per product_id first and then average those per category, and every product gets one vote instead. On this data that flips the answer: 'Beauty', where one product has a single linked sale instead of two, falls from 700.92 to about 674.51, and 'Books' slides into third. Same columns, same shape, wrong leaderboard.

Getting there

Step 1: Put a category on every sale

Inner-join transactions to products on product_id. A sale with an empty product_id matches nothing and drops out. That is the right outcome: it has no category to be averaged into.

Step 2: Average the joined rows, not the products

Group by p.category and take AVG(t.total_amount) directly over the joined rows. One sale is one row, so one sale is one vote. The zero-dollar voided sale and the refund stay in, because they are real sales. 'Beauty' ends up averaging 17 rows.

Step 3: Sort, then cut at three

Sort on avg_amount DESC, then apply LIMIT 3. The p.category ASC key only matters if two averages tie exactly. Here the cut falls between 'Beauty' at 700.92 and 'Automotive' at 698.54, so that key stays in reserve.

SELECT p.category AS category, AVG(t.total_amount) AS avg_amount FROM transactions t JOIN products p ON t.product_id = p.product_id GROUP BY p.category ORDER BY avg_amount DESC, p.category ASC LIMIT 3

Average of sales

AVG(t.total_amount) over the joined rows. 'Beauty' averages 17 sales to 700.92 and takes third.

Average of product averages

AVG(product_avg) over one row per product_id. 'Beauty' averages 9 products to about 674.51, and 'Books' at 681.99 takes third.

WITH product_avgs AS (
  SELECT product_id, AVG(total_amount) AS product_avg
  FROM transactions
  GROUP BY product_id
)
SELECT p.category, AVG(pa.product_avg) AS avg_amount
FROM product_avgs pa
JOIN products p ON pa.product_id = p.product_id
GROUP BY p.category
ORDER BY avg_amount DESC
LIMIT 3

Common follow-up questions

• How would you get the same answer if `transactions` were pre-rolled to one row per `product_id` per day? (Tests whether you carry sums and counts instead of averages.)
• Should refunds like the -45.00 sale count toward a category's average? (Tests whether you ask about the business rule before filtering on `total_amount`.)
• How would you report how much revenue the inner join left out because `product_id` was empty? (Tests reasoning about unmatched join keys.)
• What happens at the cut if the fourth category ties the third exactly? (Tests what the `category` key does just before `LIMIT 3`.)