Second Purchase

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

Problem

Someone who bought only once has no second purchase to study, so they should not appear at all. An onboarding analyst wants every other user's second purchase from the transactions table, going by transaction date, with that purchase's amount and date.

The first buy is curiosity. The second is commitment.

Tables

transactions

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

Expected output

Worked solution and explanation

Slot two, row intact

This is per-user positional selection dressed up as onboarding research. Out of every user's purchase history, keep the purchase sitting in slot two by date, and keep it whole: its total_amount and its transaction_date have to come from the same row. Anyone can find a user's first purchase with MIN(transaction_date). What separates candidates is numbering rows inside each user's history and then keeping exactly one of them. Collapse to one row per user and the second purchase is gone (user 197 comes back with its first purchase date, not its second). Number across the whole table instead and you get one purchase for the entire customer base instead of fifteen.

Getting there

Step 1: Number purchases inside each user

PARTITION BY user_id restarts the count for every user, and ORDER BY transaction_date decides who comes first. ROW_NUMBER() hands out 1, 2, 3 with no gaps and no repeats, so every user has at most one row numbered 2. The numbered CTE carries total_amount and transaction_date alongside rn, so the row you keep is the real purchase and not a reconstruction.

Step 2: Filter on the number one level up

Window results do not exist yet when WHERE runs, so WHERE ROW_NUMBER() OVER (...) = 2 is an error. Compute rn in numbered and filter rn = 2 in the outer query: number first, then select.

Step 3: Let single-purchase users fall away

A user with one purchase only ever gets rn = 1, so rn = 2 drops them with no extra clause. You need no HAVING COUNT(*) > 1 and no outer join that would produce a NULL second purchase: the missing slot-two row is the exclusion. On this data every one of the 15 buyers has a dozen or more purchases, so all 15 appear.

WITH numbered AS (
    SELECT user_id, total_amount, transaction_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS rn
    FROM transactions
)
SELECT user_id, total_amount, transaction_date
FROM numbered
WHERE rn = 2

Collapse per user

GROUP BY user_id with MIN(transaction_date) gives one row per user, but it is the first purchase. A bare total_amount next to the aggregate comes from whichever row the engine picks. Nothing in an aggregate says 'the second one'.

Number per user

ROW_NUMBER() keeps every row and labels its position, so rn = 2 selects a real row with its own total_amount and transaction_date already attached.

SELECT user_id, MIN(transaction_date) AS transaction_date
FROM transactions
GROUP BY user_id

Common follow-up questions

• Now return each user's second purchase and the days elapsed since their first. (Tests reading the neighboring row (`LAG`) in the same pass as the numbering.)
• Two purchases share a `transaction_date` and finance wants the lower `transaction_id` to win. What changes? (Tests adding a deterministic tiebreaker to the ordering.)
• Users with exactly one purchase should now appear with an empty second purchase. How do you change it? (Tests keeping unmatched users: a per-user list outer-joined to the slot-two rows.)
• At 150M rows with a few users holding most purchases, where does this query get slow? (Tests recognizing that a skewed `user_id` puts one huge partition on a single worker.)