Extreme Category Totals

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

L5

Asked in

PayU

Problem

For this calendar year, we're comparing product categories by their total sales. Combine each transaction with its product to get the category, then return the three categories with the highest totals and the three with the lowest, from most to least.

The highest and the lowest. Both are interesting.

Tables

transactions

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

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

This is a top-three-and-bottom-three in one result, dressed up as a category review. Anyone can sum the money per category. What separates candidates is taking both ends of the same sorted list without building it twice, keeping a category that sits level with third place, and filtering to 2026 without tripping over types. On this data Books and Clothing tie for third at the top. Two LIMIT 3 cuts return one of them, picked arbitrarily, and quietly drop the other.

Build it up

Step 1: Join transactions to products

transactions carries product_id but not category, so inner join to products on product_id to attach it. Sales with no product_id have no category to land in and drop out here, which is right for a per-category view.

Step 2: Compare the year as a number

strftime('%Y', t.transaction_date) returns the year as text, and text never equals the number 2026. Without a conversion the filter matches nothing and every category vanishes, with no error to warn you. CAST(strftime('%Y', t.transaction_date) AS INTEGER) puts integers on both sides of the =.

Step 3: Aggregate once into a CTE

WITH yearly AS (SELECT category, SUM(total_amount) AS total ... GROUP BY category). Build the per-category total once, so the fact table is scanned a single time and everything after works on about ten rows.

Step 4: Take the three highest and three lowest

Reference the CTE against itself. A category belongs at the top when fewer than three categories out-total it: (SELECT COUNT(*) FROM yearly h WHERE h.total > c.total) < 3. Mirror it with l.total < c.total for the bottom. Because the counts are strictly greater or strictly lower, a tie at either cutoff brings every level category along, where a plain LIMIT 3 would cut one off.

The solution

WITH yearly AS (
  SELECT
    p.category AS category,
    SUM(t.total_amount) AS total
  FROM transactions t
  JOIN products p ON t.product_id = p.product_id
  WHERE CAST(strftime('%Y', t.transaction_date) AS INTEGER) = 2026
  GROUP BY p.category
)
SELECT c.category, c.total
FROM yearly c
WHERE (SELECT COUNT(*) FROM yearly h WHERE h.total > c.total) < 3
   OR (SELECT COUNT(*) FROM yearly l WHERE l.total < c.total) < 3
ORDER BY c.total DESC

UNION of two LIMIT 3 (drops ties)

(SELECT ... ORDER BY total DESC LIMIT 3) UNION ALL (SELECT ... ORDER BY total ASC LIMIT 3). Simple to read, but LIMIT 3 cuts at exactly three, so Books or Clothing goes missing. The two halves also need a final sort.

CTE + correlated counts (keeps ties)

Build yearly once, then keep any category with fewer than three strictly-greater or strictly-lower totals. It is the same single scan of the fact table, and a boundary tie brings every level category along.

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Rewrite the year filter to be sargable on the partition key. (Tests whether you know that extracting the year with `strftime` blocks partition pruning, and that a half-open range on `transaction_date` is the fix.)
• Return categories with zero sales this year as part of the low end. (Pushes you to start from `products` with a `LEFT JOIN` and wrap the sum in `COALESCE`, so empty categories land at the bottom.)
• The business wants exactly three rows per side even when totals tie. What changes? (Probes the difference between a counting cutoff and row numbering at the boundary, and how each treats ties.)
• How would you extend this to top five and bottom five without scanning the fact table twice? (Checks that the cutoff is a parameter on the single `yearly` build, not a second pass.)