+-------------+----------+
|Column Name |Type|+-------------+----------+
| user_id | int || time_stamp | datetime || amount | int |+-------------+----------+
(user_id, time_stamp) is the primarykey (combination of columns withuniquevalues) for this table.
Eachrowcontains information about the purchase time and the amount paid for the userwith 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.
Input:
Purchases table:+---------+---------------------+--------+| user_id | time_stamp | amount |+---------+---------------------+--------+|1|2022-04-2009:03:00|4416||2|2022-03-1919:24:02|678||3|2022-03-1812:03:09|4523||3|2022-03-3009:43:42|626|+---------+---------------------+--------+startDate =2022-03-08, endDate =2022-03-20, minAmount =1000Output:
+----------+| user_cnt |+----------+|1|+----------+Explanation:
Out of the three users, only User 3is 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 3is the only user who had a purchase that satisfies both conditions.
CREATEFUNCTION getUserIDs(startDate DATE, endDate DATE, minAmount INT) RETURNS INT
BEGINRETURN (
#Write your MySQL query statement below.
SELECTCOUNT(DISTINCT user_id)
FROM Purchases
WHERE (time_stamp BETWEEN startDate AND endDate) AND amount >= minAmount
);
END