Top Product Category by Transactions

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

Our all-time category leaderboard crowns a single winner and hides how the lead has changed hands from year to year. For each of the three latest calendar years in transactions, show the year, the products category with the most purchases that year and its purchase count, keeping every category tied for a year's lead.

Organic purchases, no marketing nudge. Which category wins?

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 really is

This is a per-group arg-max with ties, dressed up as a leaderboard review. Anyone can count transactions per year and category. The trick is that the maximum you compare against has to belong to the same year. Reach for one global MAX and every year whose leader sold less than the busiest year's leader quietly drops out, so a three-year report comes back with one row in it.

The second quiet requirement is the window. 'The three latest years' is a fact about transactions, not about today's calendar, so the bound is computed from the table. The data runs back to 2022; hardcode the years, or forget the bound, and old leaders leak into the report.

Building it

Step 1: Anchor the window with a scalar subquery

SELECT MAX(EXTRACT(YEAR FROM latest.transaction_date)) - 2 FROM transactions latest returns one number: the earliest year still in scope. The WHERE compares each row's EXTRACT(YEAR FROM t.transaction_date) against it, so the older years never reach the counts in y.

Step 2: Bucket by year and count per category

DATE_TRUNC('year', t.transaction_date) collapses every date to its year start, year_start, which is the grouping key. Inner-join transactions to products on product_id to pull category, then COUNT(*). Rows with a NULL product_id have no category and fall out of the join. Out-of-stock products still count: in_stock describes today, not the sale.

Step 3: Self-join the counts on the year bucket

rival is the same per-year, per-category count over every year. Joining it to y on year_start lets each row see every competitor in its own year, so HAVING y.transaction_count = MAX(rival.transaction_count) keeps that year's leader and nothing else. Co-leaders both pass.

Step 4: Label the bucket for the report

TO_CHAR(y.year_start, 'YYYY') turns the January-first timestamp into the plain year a reader expects. The join and the grouping stay on the bucket itself, so the label is pure presentation.

SELECT TO_CHAR(y.year_start, 'YYYY') AS sales_year,
       y.category,
       y.transaction_count
FROM (
  SELECT DATE_TRUNC('year', t.transaction_date) AS year_start,
         p.category,
         COUNT(*) AS transaction_count
  FROM transactions t
  JOIN products p ON t.product_id = p.product_id
  WHERE EXTRACT(YEAR FROM t.transaction_date) >= (
    SELECT MAX(EXTRACT(YEAR FROM latest.transaction_date)) - 2
    FROM transactions latest
  )
  GROUP BY year_start, p.category
) y
JOIN (
  SELECT DATE_TRUNC('year', rt.transaction_date) AS year_start,
         rp.category,
         COUNT(*) AS transaction_count
  FROM transactions rt
  JOIN products rp ON rt.product_id = rp.product_id
  GROUP BY year_start, rp.category
) rival ON rival.year_start = y.year_start
GROUP BY y.year_start, y.category, y.transaction_count
HAVING y.transaction_count = MAX(rival.transaction_count);

One global maximum

HAVING COUNT(*) = (SELECT MAX(cnt) FROM ...) with no year scoping compares one year's category against the busiest category of any year. Only the single peak year survives; the other two vanish.

Maximum per year

Joining rival to y on year_start means MAX(rival.transaction_count) is that year's peak. Every year reports its own leader, ties included.

Common follow-up questions

• How would you return the runner-up category per year as well? (Moves from matching a maximum to positioning within each year, and how ties shift the second slot.)
• What changes if the leaderboard should be per month within the window? (Tests whether the grain change flows through `DATE_TRUNC`, the `TO_CHAR` label and the self-join key together.)
• Should a category with a single sale in a quiet year count as that year's leader? (Probes adding a minimum-volume threshold and where it belongs relative to the per-year maximum.)