Repeat Purchase Window

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

An analyst building a churn model needs the customers whose buying rhythm has slipped: the ones who once let an unusually long stretch open up between one purchase and the very next, yet came back rather than going silent for good. From the transactions table, find the users who at some point had 65 to 80 days between consecutive purchases, and return each qualifying user ID once, smallest first.

The retention squad is looking for repeat purchasers.

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

Underneath the churn story, this is a per-customer inter-purchase gap detection. For each customer, order their purchases by date, measure how far each one sits from the purchase right before it, and keep the customers whose gap lands inside a two-sided day band. The trap is the band's shape. Every customer here re-buys within days or weeks and, somewhere across the years, lets a lapse of many months open up. So a one-sided test ('gap of at least 65 days' or 'gap of at most 80 days') lets all fifteen customers through and flags your regulars and your fully-lapsed alike. Only fencing both ends isolates the twelve whose cadence genuinely drifted into the moderate-lapse band.

Walking the solution

Step 1: Look up each customer's prior purchase date

You cannot see a gap without the neighbouring row's date. LAG(transaction_date) OVER (PARTITION BY user_id ORDER BY transaction_date) hands each purchase the date of that customer's immediately preceding purchase. Partitioning by user_id keeps the comparison inside one customer; ordering by date is what makes 'the one before' meaningful.

Step 2: Turn two dates into a gap in days

julianday() turns each TEXT date into a day number, so julianday(transaction_date) - julianday(prev_date) is the gap in whole days. Real date arithmetic, not string comparison, is what keeps month and year boundaries correct. A customer's first purchase has a NULL prev_date, so its gap is NULL and never qualifies.

Step 3: Fence both ends of the window

BETWEEN 65 AND 80 does two jobs at once. The lower bound drops the short in-season gaps every customer has, and the upper bound drops the lapses that ran longer, across seasons and years. Remove either bound and the filter collapses to every customer. Both ends carry weight, which is exactly why the band must be two-sided.

Step 4: Return each customer once, in order

A customer who slips into the band more than once would otherwise appear several times, so DISTINCT collapses them to a single row. Sorting by user_id gives the smallest-first list the prompt asks for.

The solution

SELECT DISTINCT user_id
FROM (
  SELECT user_id,
         transaction_date,
         LAG(transaction_date) OVER (PARTITION BY user_id ORDER BY transaction_date) AS prev_date
  FROM transactions
)
WHERE julianday(transaction_date) - julianday(prev_date) BETWEEN 65 AND 80
ORDER BY user_id

Common follow-up questions

• Instead of a yes/no list, how would you return how many times each customer slipped into the 65-to-80-day band? (Moves from a boolean to a magnitude: `DISTINCT` becomes a grouped count.)
• What does your gap expression evaluate to for each customer's very first purchase, and why does that row never wrongly qualify? (Edge-case reasoning about `LAG` returning NULL on the first row of a partition.)
• If `transactions` grew to billions of rows, which part of this query becomes the bottleneck, and what would you change? (Scaling intuition around the window sort at high cardinality.)