Watch 2 video solutions for The Users That Are Eligible for Discount, a easy level problem involving Database. This walkthrough by Everyday Data Science has 1,406 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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 IDs of the users that are eligible for a discount.
Return the result table ordered by user_id.
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_id | +---------+ | 3 | +---------+ 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 Number of Users That Are Eligible for Discount.
Problem Overview: You need to return the users who qualify for a discount based on their email format. The task is essentially a filtering problem: scan the Users table and keep only rows where the email satisfies the required domain pattern.
Approach 1: SQL Filtering with LIKE / REGEXP (O(n) time, O(1) space)
The solution performs a straightforward scan of the Users table and filters rows using a pattern match on the mail column. In MySQL, this is typically done using LIKE or REGEXP depending on how strict the validation needs to be. The key idea is to match emails that end with the required domain (for example '%@leetcode.com'). Since SQL evaluates the predicate for each row exactly once, the runtime is O(n) where n is the number of records. The query uses constant extra space because it only returns filtered rows without additional data structures.
Pattern matching is a common technique in database problems. The database engine scans each row, evaluates the predicate, and outputs matching records. When the requirement is strictly suffix-based, LIKE '%@domain.com' is usually enough. When stricter validation rules exist, MySQL REGEXP gives more control over allowed characters before the domain.
This approach works well because the task does not require joins, grouping, or aggregation. You simply iterate over the dataset once and apply a boolean condition to each row. That makes it both easy to write and efficient for large datasets.
From an interview perspective, this problem tests familiarity with SQL filtering and pattern matching rather than complex query construction. Recognizing that the entire problem reduces to a WHERE clause is the main insight. Topics such as SQL filtering and string pattern matching appear frequently in database interview questions.
Recommended for interviews: The direct filtering query using LIKE or REGEXP. It shows you understand how SQL handles pattern matching and how to efficiently filter rows without unnecessary joins or subqueries.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LIKE pattern filtering | O(n) | O(1) | When checking if emails end with a specific domain suffix |
| REGEXP pattern validation | O(n) | O(1) | When stricter validation of allowed characters is required before the domain |
