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 -
amountis a positive integer -
time_stampis in valid datetime format -
Date parameters are provided as
startDateandendDate -
minAmountis a positive integer parameter
Visualization
Tap to expand
Understanding the Visualization
1
Input
Purchases table with user transactions
2
Filter
Apply date range and amount criteria
3
Count
Count distinct eligible users
Key Takeaway
🎯 Key Insight: Use filtering conditions with COUNT(DISTINCT) to efficiently count unique users meeting criteria
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code