The Users That Are Eligible for Discount

Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


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](https://leetcode.com/problems/the-number-of-
users-that-are-eligible-for-discount/).

Solution

Method 1 - Filter and Group by User

We filter purchases by the date and amount, then select distinct user IDs. The query is the same for MySQL, PostgreSQL, and Oracle with minor syntax differences for date handling.

Code

1
2
3
4
5
6


SELECT DISTINCT user_id
FROM Purchases
WHERE time_stamp >= CONCAT(@startDate, ' 00:00:00')
  AND time_stamp <= CONCAT(@endDate, ' 00:00:00')
  AND amount >= @minAmount
ORDER BY user_id;

1
2
3
4
5
6


SELECT DISTINCT user_id
FROM Purchases
WHERE time_stamp >= TO_DATE(:startDate, 'YYYY-MM-DD')
  AND time_stamp <= TO_DATE(:endDate, 'YYYY-MM-DD')
  AND amount >= :minAmount
ORDER BY user_id;

1
2
3
4
5
6


SELECT DISTINCT user_id
FROM Purchases
WHERE time_stamp >= (@startDate || ' 00:00:00')::timestamp
  AND time_stamp <= (@endDate || ' 00:00:00')::timestamp
  AND amount >= @minAmount
ORDER BY user_id;

Complexity

⏰ Time complexity: O(N) (N = number of rows in Purchases)
🧺 Space complexity: O(U) (U = number of unique users)

Problem#

Examples#

Solution#

Method 1 - Filter and Group by User#

Code#

Complexity#