The Number of Users That Are Eligible for Discount - Problem

You are given a table Purchases that contains information about user purchases including the user ID, timestamp, and purchase amount.

Problem: A user is eligible for a discount if they had at least one purchase in the inclusive interval [startDate, endDate] with an amount of at least minAmount.

Write a SQL query to find the number of users that are eligible for a discount based on the given criteria.

Note: Both startDate and endDate should be considered as the start of the day (e.g., 2022-03-05 means 2022-03-05 00:00:00).

Table Schema

Purchases
Column Name Type Description
user_id PK int ID of the user who made the purchase
time_stamp PK datetime Timestamp when the purchase was made
amount int Purchase amount in cents or smallest currency unit
Primary Key: (user_id, time_stamp)
Note: The combination of user_id and time_stamp forms the primary key. Each row represents a unique purchase.

Input & Output

Example 1 — Basic Discount Eligibility
Input Table:
user_id time_stamp amount
1 2022-03-08 14:20:57 250
2 2022-03-07 14:20:57 150
1 2022-03-09 14:20:57 300
Output:
eligible_users
1
💡 Note:

With criteria: startDate = '2022-03-08', endDate = '2022-03-09', minAmount = 200:

  • User 1 has purchases on 2022-03-08 (amount 250) and 2022-03-09 (amount 300) - both qualify
  • User 2 has a purchase on 2022-03-07 (outside date range) - doesn't qualify
  • Result: 1 eligible user
Example 2 — No Eligible Users
Input Table:
user_id time_stamp amount
1 2022-03-08 14:20:57 100
2 2022-03-09 14:20:57 150
Output:
eligible_users
0
💡 Note:

With criteria: startDate = '2022-03-08', endDate = '2022-03-09', minAmount = 200:

  • User 1 has purchase amount 100 (below minimum)
  • User 2 has purchase amount 150 (below minimum)
  • Result: 0 eligible users

Constraints

  • 1 ≤ user_id ≤ 1000
  • amount is a positive integer
  • time_stamp is in valid datetime format
  • Date parameters are provided as startDate and endDate
  • minAmount is a positive integer parameter

Visualization

Tap to expand
Users Eligible for Discount INPUT Purchases Table user_id time_stamp amount 1 2022-04-20 1000 2 2022-04-15 500 3 2022-04-18 800 1 2022-04-22 200 Parameters startDate: '2022-04-15' endDate: '2022-04-20' minAmount: 500 Data Structure - Table with purchase records - Each row: user, date, amount - Multiple purchases per user ALGORITHM STEPS 1 Filter by Date Range WHERE time_stamp BETWEEN startDate AND endDate 2 Filter by Amount AND amount >= minAmount Keep qualifying purchases 3 Get Distinct Users SELECT DISTINCT user_id Remove duplicates 4 Count Users COUNT(DISTINCT user_id) Return final count SQL Query SELECT COUNT(DISTINCT user_id) FROM Purchases WHERE time_stamp BETWEEN startDate AND endDate AND amount >= minAmount FINAL RESULT Filtering Process User 1, 2022-04-20, 1000 OK User 2, 2022-04-15, 500 OK User 3, 2022-04-18, 800 OK User 1, 2022-04-22, 200 X Distinct Eligible Users 1 2 3 OUTPUT 3 Key Insight: Use COUNT(DISTINCT user_id) to avoid counting the same user multiple times if they have multiple qualifying purchases. The BETWEEN clause handles inclusive date range filtering, and combining conditions with AND ensures all criteria are met simultaneously. TutorialsPoint - The Number of Users That Are Eligible for Discount | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
25.4K Views
Medium Frequency
~8 min Avg. Time
890 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