Discount-Eligible User List in Policy Window

Easy

A retail analytics team tracks shopper transactions and a one-row promotion policy. Table: Purchases user_id: shopper identifier time_stamp: transaction timestamp in format YYYY-MM-DD HH:MM:SS amount: transaction value Table: DiscountPolicy start_date: campaign start date (YYYY-MM-DD) end_date: campaign end date (YYYY-MM-DD) min_amount: minimum transaction amount required for eligibility Eligibility logic: A shopper is considered eligible if they have at least one purchase where: amount >= min_amount time_stamp is inside the inclusive interval [start_date 00:00:00, end_date 00:00:00] Important boundary interpretation: Both policy dates are interpreted at midnight. This means end_date represents exactly end_date 00:00:00, not the end of that day. Task: Return the IDs of all eligible shoppers. Output requirements: Return exactly one column named user_id. Return each user_id at most once. Sort the result by user_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Purchases:
| user_id | time_stamp           | amount |
|---------|----------------------|--------|
| 1       | 2022-04-20 09:03:00  | 4416   |
| 2       | 2022-03-19 19:24:02  | 678    |
| 3       | 2022-03-18 12:03:09  | 4523   |
| 3       | 2022-03-30 09:43:42  | 626    |

DiscountPolicy:
| start_date | end_date   | min_amount |
|------------|------------|------------|
| 2022-03-08 | 2022-03-20 | 1000       |

Output

[
  {"user_id": 3}
]

Explanation

Only user 3 has a purchase that satisfies both threshold and policy-window conditions.

Example 2

Input

Purchases:
| user_id | time_stamp           | amount |
|---------|----------------------|--------|
| 10      | 2024-10-10 00:00:00  | 700    |
| 10      | 2024-10-12 00:00:00  | 900    |
| 20      | 2024-10-12 00:00:01  | 900    |
| 30      | 2024-10-09 23:59:59  | 900    |

DiscountPolicy:
| start_date | end_date   | min_amount |
|------------|------------|------------|
| 2024-10-10 | 2024-10-12 | 700        |

Output

[
  {"user_id": 10}
]

Explanation

The interval includes both boundary midnights. Rows one second before/after the boundaries are excluded.

Example 3

Input

Purchases:
[]

DiscountPolicy:
| start_date | end_date   | min_amount |
|------------|------------|------------|
| 2023-01-01 | 2023-12-31 | 500        |

Output

[]

Explanation

With no purchases, no shopper can qualify.

Constraints

0 <= number of rows in Purchases <= 900
DiscountPolicy contains exactly one row
1 <= user_id <= 1000000000
0 <= amount <= 100000
0 <= min_amount <= 100000
(user_id, time_stamp) pairs are unique in Purchases
time_stamp format is YYYY-MM-DD HH:MM:SS
start_date and end_date format is YYYY-MM-DD
start_date <= end_date
A purchase qualifies only when both threshold and inclusive midnight-window checks pass
Return exactly one column named user_id
Do not return duplicate user IDs
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: | user_id | time_stamp | amount | |---------|----------------------|--------| | 1 | 2022-04-20 09:03:00 | 4416 | | 2 | 2022-03-19 19:24:02 | 678 | | 3 | 2022-03-18 12:03:09 | 4523 | | 3 | 2022-03-30 09:43:42 | 626 | DiscountPolicy: | start_date | end_date | min_amount | |------------|------------|------------| | 2022-03-08 | 2022-03-20 | 1000 |

Purchases: | user_id | time_stamp | amount | |---------|----------------------|--------| | 10 | 2024-10-10 00:00:00 | 700 | | 10 | 2024-10-12 00:00:00 | 900 | | 20 | 2024-10-12 00:00:01 | 900 | | 30 | 2024-10-09 23:59:59 | 900 | DiscountPolicy: | start_date | end_date | min_amount | |------------|------------|------------| | 2024-10-10 | 2024-10-12 | 700 |