Table: Purchases
+-------------+----------+ | Column Name | Type | +-------------+----------+ | user_id | int | | time_stamp | datetime | | amount | int | +-------------+----------+ (user_id, time_stamp) is the primary key (combination of columns with unique values) for this table. Each row contains information about the purchase time and the amount paid for the user with ID user_id.
A user is eligible for a discount if they had a purchase in the inclusive interval of time [startDate, endDate] with at least minAmount amount. To convert the dates to times, both dates should be considered as the start of the day (i.e., endDate = 2022-03-05 should be considered as the time 2022-03-05 00:00:00).
Write a solution to report the number of users that are eligible for a discount.
The result format is in the following example.
Example 1:
Input: Purchases table: +---------+---------------------+--------+ | 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 | +---------+---------------------+--------+ startDate = 2022-03-08, endDate = 2022-03-20, minAmount = 1000 Output: +----------+ | user_cnt | +----------+ | 1 | +----------+ Explanation: Out of the three users, only User 3 is eligible for a discount. - User 1 had one purchase with at least minAmount amount, but not within the time interval. - User 2 had one purchase within the time interval, but with less than minAmount amount. - User 3 is the only user who had a purchase that satisfies both conditions.
Important Note: This problem is basically the same as The Users That Are Eligible for Discount.
Problem Overview: The task asks you to count how many users qualify for a discount based on specific eligibility conditions defined in the database tables. You need to filter users that satisfy the rules and return the total number of qualifying users.
Approach 1: Direct Filtering with COUNT (O(n) time, O(1) space)
This problem is solved with a straightforward SQL query. Scan the relevant table and apply the eligibility rules using a WHERE clause. Each row is checked against the required conditions (such as account activity, registration timing, or other attributes defined in the schema). After filtering, use COUNT() to return the total number of users that match the criteria.
The key insight is that SQL engines are optimized for set-based filtering. Instead of iterating manually, you express the eligibility logic declaratively using conditions. The database scans the table once and counts the qualifying rows. If the problem involves multiple tables, a simple JOIN combined with COUNT(DISTINCT ...) ensures each eligible user is counted only once.
This pattern appears frequently in database interview questions. You combine row filtering, optional joins, and aggregation to produce a single numeric result. Understanding when to use COUNT(*) versus COUNT(DISTINCT column) is especially useful when joins can introduce duplicate rows.
The query runs in O(n) time where n is the number of scanned rows. Space complexity is O(1) because the database only maintains an aggregate counter. Concepts used here fall under SQL querying and aggregation.
Recommended for interviews: The direct filtering approach is exactly what interviewers expect. It demonstrates that you can translate business rules into SQL conditions and use aggregation correctly. There is no need for subqueries or complex logic unless duplicates must be removed with DISTINCT.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Direct Filtering with COUNT | O(n) | O(1) | Best for most SQL counting problems where rows can be filtered directly using WHERE |
| JOIN + COUNT(DISTINCT) | O(n) | O(1) | Useful when eligibility depends on data across multiple tables and duplicates must be removed |
LeetCode 2205 "Number of Users Eligible for Discount" Interview SQL Question Detailed Explanation • Everyday Data Science • 1,356 views views
Watch 1 more video solutions →Practice The Number of Users That Are Eligible for Discount with our built-in code editor and test cases.
Practice on FleetCode