admin@onlinelearningcenter.in (+91) 7 999 01 02 03

Shopping on multiple days in SQL

Ayush Dixit
15 Posts

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.

 

 

Published By : Ayush Dixit
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Comments

Jquery Comments Plugin