00:00:00

Description

Editorial

Discount-Eligible User List in Policy Window

EASY15 pts

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

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

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

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.

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 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

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}]

Loading problem...

101

00:00:00

Description

Editorial

Discount-Eligible User List in Policy Window

EASY15 pts

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

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

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

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.

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 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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Purchases =

DiscountPolicy =

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

Discount-Eligible User List in Policy Window

Hints

Discount-Eligible User List in Policy Window

Hints