Returning Buyers

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

Amazon

Problem

Each row of transactions is one purchase with its user_id and transaction_date, and a purchase brought its buyer back when the same user bought again between one and seven days later, both ends included. List those purchases as the user and the date of the earlier purchase, one row per user and day.

They came back and bought again.

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

What this really is

This is a bounded self-join on time dressed up as retention. No single row in transactions knows whether its buyer came back, so you have to pair each purchase with the same user's later ones. Anyone can write the self-join. The part that separates candidates is the closed day window: over the years this data covers, every buyer returns eventually, so a join that only says 'later' flags almost every purchase except each user's last one.

The second quiet trap is fan-out. One earlier purchase can be followed by two or three returns inside the same week, and each pairing is its own joined row. The question asks for purchase days, not pairings, so the result has to collapse back to one row per user and day.

Building it

Step 1: Alias transactions as earlier and later

Role names beat t1 and t2: when you read DATEDIFF('day', earlier.transaction_date, later.transaction_date) the sign of the gap is obvious. Join on later.user_id = earlier.user_id so a purchase is only ever compared with its own buyer's history.

Step 2: Order the pair and bound the gap

later.transaction_date > earlier.transaction_date fixes the direction so no pair appears mirrored. Then DATEDIFF(...) BETWEEN 1 AND 7 does the real work: the 1 excludes same-day repeats, the 7 is what turns 'eventually came back' into 'came back that week'. BETWEEN is inclusive, so exactly one day and exactly seven days both count.

Step 3: Return the earlier side and collapse with DISTINCT

Select earlier.user_id and earlier.transaction_date. DISTINCT folds the multiple returns that matched one purchase day into a single row, and it also folds two purchases on the same earlier day that both led to a return.

SELECT DISTINCT earlier.user_id, earlier.transaction_date
FROM transactions earlier
INNER JOIN transactions later
  ON later.user_id = earlier.user_id
 AND later.transaction_date > earlier.transaction_date
 AND DATEDIFF('day', earlier.transaction_date, later.transaction_date) BETWEEN 1 AND 7

After-only join

later.transaction_date > earlier.transaction_date and nothing else. Every purchase with any later purchase qualifies, which over several years is nearly all of them. The result looks plausible and is useless.

Bounded window

Add DATEDIFF('day', ...) BETWEEN 1 AND 7. Only purchases followed by a return that same week survive, which is the signal the question is about.

Common follow-up questions

• How would you return only each user's first purchase that brought them back? (Tests moving from a set of qualifying rows to a per-user earliest pick.)
• What changes if `transaction_date` carries a time of day and the window means 24 to 168 hours? (Tests choosing the right unit for the gap and the edge behavior at the bounds.)
• How would you avoid the pairwise blow-up for a buyer with thousands of purchases? (Tests replacing the range self-join with an ordered neighbour comparison.)