Discount-Eligible Users in Policy Window

Medium

A commerce platform stores purchase events and a one-row discount policy. Table: Purchases user_id: shopper identifier time_stamp: purchase timestamp in format YYYY-MM-DD HH:MM:SS amount: amount paid in that purchase Table: DiscountPolicy start_date: campaign start date (YYYY-MM-DD) end_date: campaign end date (YYYY-MM-DD) min_amount: minimum purchase amount required for eligibility Eligibility definition: A user is discount-eligible if they have at least one purchase that satisfies both conditions: amount >= min_amount purchase time is within the inclusive interval [start_date 00:00:00, end_date 00:00:00] Important time rule: Both start_date and end_date are interpreted at midnight. For example, if end_date = 2022-03-05, then the upper bound is exactly 2022-03-05 00:00:00 (not the end of that day). Task: Return one row with one column user_cnt representing the number of distinct eligible users. 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_cnt": 1}
]

Explanation

Only user 3 has a purchase meeting both rules. User 1 is outside the window, and user 2 is below the threshold.

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  | 1200   |
| 30      | 2024-10-09 23:59:59  | 1200   |

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

Output

[
  {"user_cnt": 1}
]

Explanation

The interval includes both boundary midnights, so user 10 qualifies. User 20 is one second after the upper bound and user 30 is before the lower bound.

Example 3

Input

Purchases:
[]

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

Output

[
  {"user_cnt": 0}
]

Explanation

No purchases means no eligible users.

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 values use format YYYY-MM-DD HH:MM:SS
start_date and end_date use format YYYY-MM-DD
start_date <= end_date
A purchase qualifies when amount >= min_amount and timestamp is within [start_date 00:00:00, end_date 00:00:00] inclusive
Return exactly one output column named user_cnt
Return exactly one output row
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one row and one column user_cnt

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 | 1200 | | 30 | 2024-10-09 23:59:59 | 1200 | DiscountPolicy: | start_date | end_date | min_amount | |------------|------------|------------| | 2024-10-10 | 2024-10-12 | 700 |