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

We group users by account status, and every segment leans toward one product category more than the rest. For each status, surface the category with the most purchases, where a purchase is a single transaction regardless of how many units it moved. Walk through the segments from the lowest account status upward, and when a status has two categories tied for the lead, let the one that comes first alphabetically take the spot.

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 marketing veneer and this is top-per-group with ties kept, where the group is account_status and the winner count is one. Everyone can join three tables and count; the ranking step is what separates candidates. Aggregate to one row per (status, category) first, then rank those groups inside each status. Reach for ROW_NUMBER and you silently pick one winner when two categories tie, dropping legitimate answers; RANK keeps every category that reaches the top count. Over 200M transactions you also have no choice but to aggregate before you rank, so you rank a few hundred groups, not rows.

Break down the requirements

Step 1: Join and count per pair

Join transactions to users (for account_status) and products (for category), then COUNT(*) grouped by (account_status, category). That collapses 200M rows to at most a few hundred groups. Note it is COUNT(*) of rows, not SUM(quantity): a five-unit basket is still one purchase, and weighting by quantity flips winners in any segment with a big-basket outlier.

Step 2: Rank inside each segment

RANK() OVER (PARTITION BY account_status ORDER BY COUNT(*) DESC). Using RANK keeps every tied top category; ROW_NUMBER would collapse a real tie to one arbitrary winner and quietly delete the rest.

Step 3: Filter and order

Keep rk = 1 and ORDER BY account_status, category. The outer sort is by category name, not by count, because all surviving rows already share the top count within their segment.

The solution

WITH ranked AS (
  SELECT
    u.account_status,
    p.category,
    COUNT(*) AS purchase_count,
    RANK() OVER (
      PARTITION BY u.account_status
      ORDER BY COUNT(*) DESC
    ) AS rk
  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
)
SELECT account_status, category, purchase_count
FROM ranked
WHERE rk = 1
ORDER BY account_status, category;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would the answer change if you used `DENSE_RANK` instead of RANK? (Same result here because we only keep rk = 1, but the values differ for non-winners; explain when each matters.)
• What if an `account_status` has zero transactions? (It disappears from the output. Discuss a LEFT JOIN from a status dimension if you need every segment present, even empty ones.)
• How would you weight by `total_amount` instead of count? (Swap COUNT(*) for SUM(`t.total_amount`) in both the aggregate and the ORDER BY of the window. Ties become much rarer.)