Top Product Categories

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

Asked in

Spark

Problem

A retail marketplace's merchandising team is spotlighting the categories that sell best among its well-reviewed products, where a product's category lives on the products table. Counting only transactions on products rated 3.0 or higher, surface the top three categories by transaction count, and include every category tied at the third spot.

Top three categories by page views.

Tables

transactions

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

products

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

Expected output

Worked solution and explanation

What this problem really is

Beneath the merchandising story this is a top-N-with-ties ranking: rank the filtered per-category counts and keep the top three standings, not the top three rows. The trap is reaching for LIMIT 3 or ROW_NUMBER(). Both cut mid-tie: when four categories share the second standing, LIMIT keeps whichever three the engine happens to emit and silently drops a genuine co-leader. DENSE_RANK() is what lets 'include everyone tied at the third spot' actually work. The WHERE p.rating >= 3.0 scope is load-bearing too: drop it and the counts flatten to two tiers and the ranking has nothing to separate.

Break down the requirements

Step 1: Scope to well-reviewed products

Inner-join transactions to products on product_id, and keep only rows where p.rating >= 3.0, so every counted sale sits on a well-reviewed product.

Step 2: Aggregate before you rank

GROUP BY p.category and COUNT(*) for the total transactions in each category.

Step 3: Rank the tiers, not the rows

Assign DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) so tied categories share a standing and the numbering has no gaps, which is what makes 'include all tied at a cutoff' well-defined.

Step 4: Cut at the third standing

Wrap the ranked set and filter WHERE rnk <= 3 in the outer query, keeping the top three distinct standings and every category tied within them.

The solution

SELECT category, txn_count, rnk
FROM (
  SELECT
    p.category AS category,
    COUNT(*) AS txn_count,
    DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM transactions t
  JOIN products p ON t.product_id = p.product_id
  WHERE p.rating >= 3.0
  GROUP BY p.category
) ranked
WHERE rnk <= 3;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you rank by total revenue instead of transaction count? (Swaps `COUNT(*)` for `SUM(total_amount)` and checks you understand the window's `ORDER BY` follows the new aggregate.)
• What changes if a product belongs to multiple categories? (Probes join cardinality and whether you'd dedupe transactions per category.)
• Top three categories per month, still keeping ties? (Forces a `PARTITION BY` on the month inside the `DENSE_RANK` window.)
• Why not solve this with a correlated subquery counting categories with a higher total? (Tests whether you can articulate the O(n^2) cost of a correlated count versus the single-pass window.)