Blog

Introduction

• 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.

Question

• 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.

Solution

• 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.

Final Solution

• 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

Submission

• Our solution is accepted by the platform.

Conclusion

• In this article we solved sql question asked by Walmart. We used windowing operation with rank, subquery techniques to solve the problem.

Bonus Tip

• If you want to upskill your SQL interview game, you can definitely check out this bestseller course