Identify Repeat Buyers Within Seven Days

Medium

A retail intelligence team wants to flag customers who come back quickly after a previous purchase. Table: Purchases purchase_id: unique purchase identifier user_id: customer identifier purchase_date: transaction date (YYYY-MM-DD) A user is considered a rapid repeat buyer if they have at least one pair of purchases where the later purchase happened within 7 calendar days of the earlier one. Same-day purchases also qualify. Task: Return all qualifying user IDs. Output requirements: Return exactly one column in this order: user_id Include each user at most once Sort by user_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Purchases:
| purchase_id | user_id | purchase_date |
|-------------|---------|---------------|
| 1           | 10      | 2024-01-01    |
| 2           | 10      | 2024-01-08    |
| 3           | 20      | 2024-01-01    |
| 4           | 20      | 2024-01-20    |
| 5           | 30      | 2024-01-05    |
| 6           | 30      | 2024-01-05    |

Output

[
  {"user_id":10},
  {"user_id":30}
]

Explanation

User 10 qualifies because Jan-08 is exactly 7 days after Jan-01. User 30 qualifies because same-day pairs count. User 20 does not qualify because the gap is 19 days.

Example 2

Input

Purchases:
| purchase_id | user_id | purchase_date |
|-------------|---------|---------------|
| 101         | 5       | 2024-02-01    |
| 102         | 5       | 2024-02-10    |
| 103         | 6       | 2024-02-02    |
| 104         | 6       | 2024-02-04    |
| 105         | 6       | 2024-02-25    |

Output

[
  {"user_id":6}
]

Explanation

User 6 has a 2-day gap between Feb-02 and Feb-04. User 5 has only a 9-day gap and does not qualify.

Example 3

Input

Purchases:
| purchase_id | user_id | purchase_date |
|-------------|---------|---------------|
| 201         | 1       | 2024-03-01    |
| 202         | 2       | 2024-03-02    |
| 203         | 3       | 2024-03-03    |

Output

[]

Explanation

Every user has only one purchase, so no user can form a qualifying pair.

Constraints

0 <= number of rows in Purchases <= 260
1 <= purchase_id <= 2000000000
1 <= user_id <= 1000000000
purchase_date uses YYYY-MM-DD format
purchase_id values are unique within a test case
A qualifying pair means date difference <= 7 days
Same-day purchases are valid qualifying pairs
Return each qualifying user only once
Return exactly one column: user_id
Sort output by user_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with one column: user_id

Purchases: | purchase_id | user_id | purchase_date | |-------------|---------|---------------| | 1 | 10 | 2024-01-01 | | 2 | 10 | 2024-01-08 | | 3 | 20 | 2024-01-01 | | 4 | 20 | 2024-01-20 | | 5 | 30 | 2024-01-05 | | 6 | 30 | 2024-01-05 |

Purchases: | purchase_id | user_id | purchase_date | |-------------|---------|---------------| | 101 | 5 | 2024-02-01 | | 102 | 5 | 2024-02-10 | | 103 | 6 | 2024-02-02 | | 104 | 6 | 2024-02-04 | | 105 | 6 | 2024-02-25 |

Purchases: | purchase_id | user_id | purchase_date | |-------------|---------|---------------| | 201 | 1 | 2024-03-01 | | 202 | 2 | 2024-03-02 | | 203 | 3 | 2024-03-03 |