00:00:00

Description

Editorial

Active Users by Seven-Day Repeat Purchases

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Users =

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

Loading problem...

101

00:00:00

Description

Editorial

Active Users by Seven-Day Repeat Purchases

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Users =

Active Users by Seven-Day Repeat Purchases

Hints

Active Users by Seven-Day Repeat Purchases

Hints