Workers Earning Above Department Average

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

L4

Problem

Inner-join products to transactions on product_id. Per (product_id, product_name), count transactions. Return product_name and transaction_count. Sort by product_id ascending.

Earning above the department average.

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

The label is not the key

This is a count per entity dressed up as a count per name. The join and the COUNT(*) are the easy part. The skill being probed is whether you group on the product's identity (product_id) or on the text you happen to display (product_name). Group by the name and any two products that share one collapse into a single row with the summed transaction_count. You get fewer than 91 rows, inflated counts on the collided names, and nobody notices because every row still looks plausible.

The sample data hides this. Each product has exactly one transaction, so the counts are all 1 and a merged row would just read 2. A wrong query can look right if you only skim the output.

Step 1: Inner-join products to transactions

Join on product_id. The statement says inner, and that is the right call: a product nobody bought has no transaction to count, so it drops out instead of appearing with a zero. A LEFT JOIN with COUNT(*) would show those products with a count of 1, which is a lie.

Step 2: Count rows with COUNT(*)

The question is how many transactions each product has, so count transaction rows. COUNT(DISTINCT user_id) answers a different question: how many buyers. A customer who bought the same product twice would be counted once.

Step 3: Group by p.product_id, p.product_name

The key goes first, and the name rides along so it can be selected. Because product_id is unique, the name is functionally determined by it, so adding the name changes nothing about the groups. It just makes the projection legal in every engine.

Step 4: Sort by p.product_id even though you do not select it

The statement asks for product_id order, but the output shows only the name. Sorting by product_name or by transaction_count is the easy slip. Sorting on a column outside the SELECT list works only because it is in the GROUP BY.

SELECT p.product_name, COUNT(*) AS transaction_count FROM products p INNER JOIN transactions t ON p.product_id = t.product_id GROUP BY p.product_id, p.product_name ORDER BY p.product_id ASC

SELECT p.product_name, COUNT(*) AS transaction_count FROM products p INNER JOIN transactions t ON p.product_id = t.product_id GROUP BY p.product_name ORDER BY p.product_id ASC

Group by the name

Products that share a product_name merge into one row with a summed count. ORDER BY p.product_id is ill-defined because a merged group has several ids, so strict engines reject it and lenient ones pick an arbitrary id.

Group by the key and the name

Each product_id is its own group, so same-named products stay separate rows. The sort key is a grouping column, so the order is deterministic and you get every product that sold.

Common follow-up questions

• How would the result change if you wanted unsold products listed with a count of 0? (Tests `LEFT JOIN` plus `COUNT(t.transaction_id)` instead of `COUNT(*)`, since `COUNT(*)` would count the null-extended row as 1.)
• Two products share a `product_name`. How would you make the output readable to a human without merging them? (Tests keeping `product_id` in the `SELECT` list, or disambiguating with `category`, instead of grouping on the label.)
• How would you return only the products with more than five transactions? (Tests `HAVING COUNT(*) > 5` versus filtering in `WHERE`, which runs before the aggregate exists.)