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
Users Eligible for Discount INPUT user_id date amount 1 2022-04-29 150 1 2022-05-01 200 2 2022-05-15 80 3 2022-05-10 250 4 2022-06-01 300 Parameters startDate: 2022-05-01 endDate: 2022-05-31 minAmount: 100 Date Range: May 2022 ALGORITHM STEPS 1 Filter by Date Range WHERE date BETWEEN startDate AND endDate 2 Filter by Amount AND amount >= minAmount 3 Select Distinct Users SELECT DISTINCT user_id 4 Order Results ORDER BY user_id ASC SELECT DISTINCT user_id FROM Purchases WHERE date BETWEEN ... AND ... AND amount >= minAmount ORDER BY user_id FINAL RESULT Filtering Process User 1 200 OK User 2 80 < 100 User 3 250 OK User 4 Out of range user_id 1 3 Output: [1, 3] Key Insight: The optimal solution uses a single SQL query with WHERE clause combining date range (BETWEEN) and amount conditions. DISTINCT ensures each user appears only once, even with multiple qualifying purchases. Time complexity: O(n log n) for sorting, Space complexity: O(k) where k is the number of eligible users. TutorialsPoint - The Users That Are Eligible for Discount | Optimal Solution

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