The Users That Are Eligible for Discount - Problem

You are given a table Purchases that contains information about user purchases with timestamps and amounts.

Problem: Find all users who are eligible for a discount. A user is eligible if they made at least one purchase with amount ≥ minAmount during the time period [startDate, endDate] (inclusive).

Important: The date parameters should be treated as the start of the day (e.g., 2022-03-05 means 2022-03-05 00:00:00).

Return the eligible user IDs ordered by user_id.

Table Schema

Purchases
Column Name Type Description
user_id PK int ID of the user making the purchase
time_stamp PK datetime Timestamp when the purchase was made
amount int Amount spent in the purchase
Primary Key: (user_id, time_stamp)
Note: Each row represents a single purchase by a user at a specific time

Input & Output

Example 1 — Basic Eligibility Check
Input Table:
user_id time_stamp amount
1 2022-03-10 14:32:00 400
2 2022-03-15 09:45:00 200
3 2022-03-12 16:20:00 500
1 2022-03-25 11:15:00 350
Output:
user_id
1
3
💡 Note:

Given parameters: startDate = '2022-03-08', endDate = '2022-03-20', minAmount = 300. User 1 has a purchase of 400 on 2022-03-10 (within range and >= 300). User 2 has amount 200 which is below minimum. User 3 has amount 500 on 2022-03-12 (eligible). User 1's purchase on 2022-03-25 is outside the date range.
Example 2 — No Eligible Users
Input Table:
user_id time_stamp amount
1 2022-03-10 14:32:00 150
2 2022-03-15 09:45:00 250
Output:
user_id
💡 Note:

With minAmount = 300, no users meet the minimum amount requirement, so the result is empty.
Example 3 — Edge Case with Exact Date Boundaries
Input Table:
user_id time_stamp amount
1 2022-03-08 00:00:00 300
2 2022-03-20 23:59:59 400
3 2022-03-21 00:00:00 500
Output:
user_id
1
2
💡 Note:

User 1 and 2 are on the boundary dates and meet criteria. User 3 is one day after the end date, so not eligible.

Constraints

  • 1 ≤ user_id ≤ 1000
  • 1 ≤ amount ≤ 1000
  • time_stamp is a valid datetime
  • The table may contain multiple purchases per user
  • startDate ≤ endDate
  • minAmount ≥ 1

Visualization

Tap to expand
User Discount Eligibility ProblemPurchases Tableuser_idtime_stampamount12022-03-1040022022-03-1520032022-03-12500WHEREdate in rangeAND amount >= 300Eligible Usersuser_id13✓ Eligible (amount >= 300)✗ Not eligible (amount < 300)
Understanding the Visualization
1
Input
Purchases table with user_id, time_stamp, amount
2
Filter
Apply WHERE conditions for date and amount
3
Output
Distinct eligible user_id list
Key Takeaway
🎯 Key Insight: Use WHERE clause with date and amount conditions to filter eligible users efficiently

Related Problems

Asked in
Amazon 15 Microsoft 12
12.5K Views
Medium Frequency
~8 min Avg. Time
342 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen