Discount-Eligible Users in Policy Window

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Purchases =

[{"user_id":1,"time_stamp":"2022-04-20 09:03:00","amount":4416},{"user_id":2,"time_stamp":"2022-03-19 19:24:02","amount":678},{"user_id":3,"time_stamp":"2022-03-18 12:03:09","amount":4523},{"user_id":3,"time_stamp":"2022-03-30 09:43:42","amount":626}]

DiscountPolicy =

[{"start_date":"2022-03-08","end_date":"2022-03-20","min_amount":1000}]

Discount-Eligible Users in Policy Window

Hints

Discount-Eligible Users in Policy Window

Hints