Product Transaction Counts

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

Asked in

NVIDIA

Problem

The inventory team needs to see sales velocity per product. Show each product's name and how many transactions reference it, ordered by product ID. Only include products that have been purchased at least once.

Show how many transactions each product has, sorted by product ID.

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

This is a semi-join wearing an inventory report's clothes. Anyone can count rows per product; what separates candidates is two quieter decisions. First, the join type: the inner JOIN itself is the 'purchased at least once' filter, so reaching for a LEFT JOIN to 'show every product' quietly breaks the brief. Second, the grain: the count belongs to product_id, not to product_name, because a name is a label, not an identity. Get the join wrong and the report grows from 91 rows to all 200 catalog products, with every never-sold item showing a count of 1.

Walking to the answer

Step 1: Let the join do the filtering

transactions holds one row per sale and products holds the name, so the two must meet on product_id. An inner JOIN keeps only products with at least one matching transaction, which is exactly the brief. The 109 catalog products nobody bought never reach the count, and neither do the 18 transactions whose product_id is NULL, because NULL matches nothing.

Step 2: Count at the product's grain

GROUP BY p.product_id, p.product_name makes the identifier the group key and carries the name along for display. COUNT(*) then counts joined rows, and each joined row is one transaction, so a repeat buy by the same user counts again. That is what velocity means; COUNT(DISTINCT t.user_id) would measure reach instead.

Step 3: Sort by a key you do not show

ORDER BY p.product_id sorts on a column the result never displays. That is legal because product_id is in the GROUP BY, and it is the catalog order the team asked for. Sorting by product_name would give alphabetical order, which is a different list.

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

Outer join to be safe

products LEFT JOIN transactions keeps all 200 catalog rows. An unsold product arrives with a NULL-padded transaction side, and COUNT(*) counts that padding row as 1, so a product nobody bought reports one sale.

Inner join on purpose

products JOIN transactions keeps only the 91 products that have a matching sale. Every surviving row is a real transaction, so COUNT(*) is an honest tally and the 'at least once' rule needs no extra clause.

Common follow-up questions

• Velocity is usually a rate. How would you report transactions per product over the last 30 days instead of all time? (Tests adding a window on `transaction_date` without disturbing the join or the grain.)
• Transaction 1335 is a voided sale with `quantity` 0, and 1603 is a refund with a negative `total_amount`. Should either count toward velocity, and how would you exclude them? (Tests reading the data instead of trusting a raw row count.)
• The catalog team now wants never-sold products listed too, with a count of 0. What changes? (Tests switching to an outer join and counting `t.transaction_id` instead of `*`.)