Identify users who have made purchases totaling more than Rs.5,000 in the last 1 month from the Current Date.
Table script is given below:
CREATE TABLE purchases (
purchase_id INT Identity (1,1) PRIMARY KEY,
user_id INT,
date_of_purchase date,
product_id INT,
amount_spent DECIMAL(10, 2)
);
INSERT INTO purchases ( user_id, date_of_purchase, product_id, amount_spent)
VALUES
(1, '2024-06-22' , 11, 1000),
(3, '2024-06-24' , 12, 4000),
(1, '2024-06-28 ', 11, 7000),
(2, '2024-06-19 ', 12, 2000),
(3, '2024-06-12 ', 12, 7000),
(1, '2024-05-15 ', 11, 8000),
(3, '2024-05-18 ', 12, 3000),
(1, '2024-05-28 ', 11, 9000),
(2, '2024-06-20 ', 12, 1500),
(3, '2024-06-25 ', 12, 6000); Solving Approach:
- Filter the last 1 month data with where clauses.
- Get the sum of amount spent based on group of User_ID.
- Filter the group using Having Clauses .
SELECT SUM(amount_spent) as Total_Spent, USER_ID
FROM purchases
WHERE date_of_purchase >= DATEADD(MONTH, -1, GETDATE())
GROUP BY USER_ID
HAVING SUM(amount_spent) > 5000
Input:
Output:
Leave a Reply