Repeat Purchases Within a Week

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

Vanguard

Problem

Which customers made two purchases within a week of each other during 2026, going by the transactions log? The retention team's repeat-buyer list needs each of those user IDs once.

They bought again within seven days.

Tables

transactions

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

Expected output

Worked solution and explanation

This is a per-customer pair search dressed up as a loyalty report. Every customer in transactions bought at least six times in 2026, so 'bought more than once' is not the question. The question is whether two of those purchases sit within seven days of each other. Count rows per user_id, or forget the day gap, and you hand back all 15 customers. The real answer is 13: 1264 and 1361 bought six times each this year, but never closer than 11 days apart.

Building the pairs

Step 1: Pair each purchase with the same customer's other purchases

Self-join transactions as earlier and later on user_id. Each row of the join is one candidate pair of purchases by one customer, which is exactly the thing the question is about.

Step 2: Put every pair in date order

later.transaction_date >= earlier.transaction_date fixes which row comes first, so the gap is never negative and no ABS is needed. The transaction_id <> check stops a row from pairing with itself, which would give every buyer a zero-day gap.

Step 3: Bound the year on earlier only

earlier.transaction_date >= '2026-01-01' is enough: the later purchase falls on or after the earlier one, and the log stops at today, so both land in 2026. A second bound on later would be redundant.

Step 4: Measure the gap in days, not characters

transaction_date is text. julianday() turns each side into a day number, so the subtraction is a real day count: exactly seven days apart passes <= 7, and same-day pairs are zero apart.

Step 5: Collapse pairs to customers

Three purchases in one week give three pairs. DISTINCT on earlier.user_id returns each customer once.

SELECT DISTINCT earlier.user_id
FROM transactions AS earlier
JOIN transactions AS later
  ON later.user_id = earlier.user_id
 AND later.transaction_id <> earlier.transaction_id
 AND later.transaction_date >= earlier.transaction_date
WHERE earlier.transaction_date >= '2026-01-01'
  AND julianday(later.transaction_date) - julianday(earlier.transaction_date) <= 7

Unordered pair with an absolute gap

Joining on t1.transaction_id < t2.transaction_id and testing ABS(...) <= 7 works, but the year bound then has to sit on both sides, because either row could be the earlier purchase.

Date-ordered pair

Joining on later.transaction_date >= earlier.transaction_date fixes which row is first, so one bound on earlier covers the whole pair and the gap needs no ABS.

Common follow-up questions

• How would you return the date each customer first hit a week-repeat in 2026? (Moves from existence to the earliest qualifying pair: `MIN(later.transaction_date)` per `user_id`.)
• What changes if the rule becomes three purchases within any seven days? (Tests whether the candidate reaches for a two-row lookback with `LAG` over date order instead of a three-way self-join.)
• Should a voided sale with `total_amount` of 0 count as a purchase? (Probes data quality: the seed holds a voided row and a refund.)