- In this article we will solve Histogram of Users and Purchases SQL question which is asked by Walmart as per DataLemur website.
If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.
- We have been given user_transaction table and we need to get for the each transaction date, the total number of users who made transaction and bought number of products.
- At first, we will use rank window operation over user_id as partition and sort it b transaction_date. The reason to do this to get the latest transaction at the top with rank 1 in the partition.
- Here is the output. As we can see user_id 115 made several transaction but rnk 1 denotes the latest transaction made by the 115 user_id.
- Now once we have the partition with us, we need to filter out all the latest transaction for each user_id. Basically filter out all the records with rnk 1.
- Once we have our base table ready we can use it as subquery and get the total_count of user and total_product purchased on the particular transaction date , grouping by transaction_date and sorting it by transaction date as expected in the question
- Our output has sorted order of the transaction date and number of user who made purchase in that transaction date along with total number of products purchased in that date.
- Here is the final solution for the question
select transaction_date, count(distinct user_id) number_of_users, count(distinct product_id) number_of_products from( select * from( select *, rank() over(partition by user_id order by transaction_date desc) rnk from user_transactions ) x where rnk =1 ) y group by transaction_date order by transaction_date
- Our solution is accepted by the platform.
- In this article we solved sql question asked by Walmart. We used windowing operation with rank, subquery techniques to solve the problem.