First Among Many

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

Meta

Problem

Each row in transactions is one purchase, however many units it moved, made by a user whose account status sits in users, of a product whose category sits in products. For every account status, find the product category with the most purchases, keeping every category that ties for the lead, and list the statuses alphabetically.

Each segment has a favorite category.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

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

Strip the segment talk away and this is a per-group argmax: inside each account_status, which category has the most transactions rows. Everyone can chain transactions to users and products and count. What separates candidates is counting the right thing and scoping 'the most' to each status. A purchase is a row, not a unit: sum quantity instead of COUNT(*) and three of the four statuses change leaders, with 'suspended' handing its spot from 'Books' to a 'Music' and 'Sports' tie. Pick the top category over the whole table instead of per status and every segment gets the same answer.

Build it in three moves

Step 1: Count purchases per status and category

Inner-join transactions to users on user_id for account_status, and to products on product_id for category, then COUNT(*) per pair in purchase_counts. One row is one purchase, so a five-unit order still counts once. The 18 transactions with no product_id have no category and drop out of the join. That is correct: they cannot lead any category, and none of the four leaders changes because of them.

Step 2: Rank categories inside each status

RANK() OVER (PARTITION BY account_status ORDER BY purchase_count DESC) restarts the ordering for every status and reads the grouped pairs, not the raw transactions. RANK gives a 1 to every category tied for the lead, which is exactly the promise the statement makes about ties.

Step 3: Keep the leaders and sort by status

Filter to status_rank = 1 and order by account_status alone. Alphabetical status is the only order asked for; co-leaders inside one status, if they ever appear, may come in any order, so no tiebreaker belongs in the sort.

The solution

WITH purchase_counts AS (
  SELECT
    u.account_status,
    p.category,
    COUNT(*) AS purchase_count
  FROM transactions t
  JOIN users u    ON t.user_id    = u.user_id
  JOIN products p ON t.product_id = p.product_id
  GROUP BY u.account_status, p.category
),
ranked AS (
  SELECT
    account_status,
    category,
    purchase_count,
    RANK() OVER (
      PARTITION BY account_status
      ORDER BY purchase_count DESC
    ) AS status_rank
  FROM purchase_counts
)
SELECT account_status, category, purchase_count
FROM ranked
WHERE status_rank = 1
ORDER BY account_status;

Counting rows

COUNT(*) per pair crowns 'Automotive' for 'active', 'Garden' for 'inactive', 'Clothing' for the pending-verification segment and 'Books' for 'suspended': four rows, one leader each.

Summing units

SUM(quantity) per pair splits 'active' three ways at 24 units, gives the pending-verification segment to 'Music' at 30 and splits 'suspended' between 'Music' and 'Sports' at 30: seven rows, three statuses wrong.

Common follow-up questions

Common follow-up questions

• Would `DENSE_RANK` change the answer? (Not for `status_rank = 1`: both give every co-leader a 1. They differ only below the top, which matters once you ask for the top two per status.)
• What if an `account_status` has no transactions at all? (It vanishes from the output. Drive the query from the statuses in `users` with an outer join if every segment must appear.)
• How would you rank by revenue instead? (Swap `COUNT(*)` for `SUM(total_amount)` in the aggregate, and decide what a refund or a voided sale with a zero or negative `total_amount` should contribute.)