You are given a transaction table that consists of transaction_id, user_id, transaction_date, product_id, and quantity.
Problem Statement:-
You need to write the query to find the number of users who purchased products on multiple days(Note that a given user can purchase multiple products on a single day).
My Approach:-
In order to solve this query, I cannot directly count the occurrence of user_id, and if it is more than one return that user_id because a given user can have more than one transaction on a single day. Hence if a given user_id has more than one distinct date associated with it means he\she has purchased products on multiple days. Following the same approach, I will write a query. (Inner query).
Since the question asked for the total number of user_ids and not the user_id itself, So I will use COUNT in the outer query.
Final Query:-
SELECT COUNT(user_id)
FROM
(
SELECT user_id
FROM transaction
GROUP BY user_id
HAVING COUNT(DISTINCT DATE(date)) > 1
) t
Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.
