Active Users by Seven-Day Repeat Purchases

Medium

A commerce analytics team is building a "quick return" user signal from raw transaction logs. Table: Users user_id: customer identifier item: purchased product name created_at: purchase timestamp amount: purchase value Data quality notes: The table can contain duplicate rows. A user can purchase many different items over time. Definition of active user: A user is active if there exists at least one pair of purchases for that same user where the later purchase happens within 7 days (inclusive) of the earlier purchase. Same-day repeat purchases also qualify. Task: Return the IDs of all active users. Output requirements: Return exactly one column named user_id Return each qualifying user only once Row order does not matter Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Users:
| user_id | item              | created_at  | amount |
|---------|-------------------|-------------|--------|
| 5       | Smart Crock Pot   | 2021-09-18  | 698882 |
| 6       | Smart Lock        | 2021-09-14  | 11487  |
| 6       | Smart Thermostat  | 2021-09-10  | 674762 |
| 8       | Smart Light Strip | 2021-09-29  | 630773 |
| 4       | Smart Cat Feeder  | 2021-09-02  | 693545 |
| 4       | Smart Bed         | 2021-09-13  | 170249 |

Output

[
  {"user_id":6}
]

Explanation

User 6 has purchases 4 days apart, so user 6 is active. User 4 has a gap of 11 days, while users 5 and 8 have only one purchase each.

Example 2

Input

Users:
| user_id | item            | created_at           | amount |
|---------|-----------------|----------------------|--------|
| 10      | Espresso Maker  | 2024-01-01 08:00:00  | 320    |
| 10      | Coffee Grinder  | 2024-01-08 08:00:00  | 210    |
| 20      | Router           | 2024-01-01 08:00:00  | 90     |
| 20      | WiFi Extender    | 2024-01-08 08:00:01  | 45     |

Output

[
  {"user_id":10}
]

Explanation

User 10 qualifies at the exact 7-day boundary. User 20 misses by one second and is excluded.

Example 3

Input

Users:
| user_id | item          | created_at           | amount |
|---------|---------------|----------------------|--------|
| 40      | Gaming Mouse  | 2024-05-10 09:30:00  | 79     |
| 40      | Gaming Mouse  | 2024-05-10 09:30:00  | 79     |
| 50      | Desk Lamp     | 2024-03-01 10:00:00  | 20     |
| 50      | Desk Lamp     | 2024-03-20 10:00:00  | 20     |

Output

[
  {"user_id":40}
]

Explanation

Duplicate timestamps still represent separate rows, so user 40 qualifies with a 0-day gap. User 50 has a 19-day gap and does not qualify.

Constraints

0 <= number of rows in Users <= 260
1 <= user_id <= 1000000000
1 <= amount <= 1000000
item is a non-empty text value
created_at is a valid ISO date or datetime string
The Users table may contain duplicate rows
A user is active if any adjacent pair in their sorted timeline has a gap <= 7 days
Same-day purchases are considered within 7 days
Return exactly one column: user_id
Return each active user at most once
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column: user_id

Users: | user_id | item | created_at | amount | |---------|-------------------|-------------|--------| | 5 | Smart Crock Pot | 2021-09-18 | 698882 | | 6 | Smart Lock | 2021-09-14 | 11487 | | 6 | Smart Thermostat | 2021-09-10 | 674762 | | 8 | Smart Light Strip | 2021-09-29 | 630773 | | 4 | Smart Cat Feeder | 2021-09-02 | 693545 | | 4 | Smart Bed | 2021-09-13 | 170249 |

Users: | user_id | item | created_at | amount | |---------|-----------------|----------------------|--------| | 10 | Espresso Maker | 2024-01-01 08:00:00 | 320 | | 10 | Coffee Grinder | 2024-01-08 08:00:00 | 210 | | 20 | Router | 2024-01-01 08:00:00 | 90 | | 20 | WiFi Extender | 2024-01-08 08:00:01 | 45 |

Users: | user_id | item | created_at | amount | |---------|---------------|----------------------|--------| | 40 | Gaming Mouse | 2024-05-10 09:30:00 | 79 | | 40 | Gaming Mouse | 2024-05-10 09:30:00 | 79 | | 50 | Desk Lamp | 2024-03-01 10:00:00 | 20 | | 50 | Desk Lamp | 2024-03-20 10:00:00 | 20 |