The Clean Aisle Numbers

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

PayPal

Problem

A customer's first purchase is the first row we recorded for them in transactions, the one with the lowest transaction_id, whatever its transaction_date says. Counting only those first purchases, we want the total amount each product category in products captured, largest first.

Clear the noise. What did each category actually earn?

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

This is a first-row-per-customer problem wearing a category sales report. Anyone can join transactions to products and sum by category. What separates candidates is shrinking each customer to one row before anything is added up, and choosing that row by transaction_id, not by date. Every customer here has 12 to 14 transactions, so a plain sum by category reports lifetime spend and lands roughly ten times too high. Most customers also have later-recorded rows carrying earlier transaction_date values, so the 'earliest date' reading credits a different purchase and moves categories around the list.

Build it in three moves

Step 1: Number each customer's transactions by transaction_id

Number rows with ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_id). First means first recorded, and transaction_id is the column that records it. The dates on this table are not in recording order and they repeat, so ordering by transaction_date could pick the wrong row and also leave the winner undefined.

Step 2: Number before you join

Compute the numbering on transactions alone, then keep rn = 1. One customer's first purchase has a NULL product_id. Number first and that customer simply contributes nothing. Join to products first and the inner join drops that row, so their second purchase gets promoted to first place and credits a category it never earned.

Step 3: Join to products, sum, sort

Inner-join the numbered rows to products on product_id for the category, keep rn = 1, take SUM(total_amount) per category as total_sales, and sort by total_sales descending.

The solution

WITH numbered_purchases AS (
    SELECT
        product_id,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY transaction_id
        ) AS rn
    FROM transactions
)
SELECT p.category, SUM(f.total_amount) AS total_sales
FROM numbered_purchases f
JOIN products p ON f.product_id = p.product_id
WHERE f.rn = 1
GROUP BY p.category
ORDER BY total_sales DESC

Common follow-up questions

• What if you wanted each customer's first purchase within every category? (Partition the numbering by `user_id` and `category`, which forces the join to `products` before numbering.)
• How would you pick the first row without a window function? (A `MIN(transaction_id)` per `user_id` joined back to `transactions`; compare the extra pass against the window's sort.)
• What changes if first later means the earliest `transaction_date`? (Tests whether the candidate makes the order total: `ORDER BY transaction_date, transaction_id`, so same-day purchases still have one winner.)