Javarevisited
Published in

Javarevisited

Histogram of Users and Purchases — SQL Question Asked By Walmart

Solving SQL Questions Asked By Walmart

Originally Published in https://asyncq.com/

Photo by Caspar Camille Rubin on Unsplash

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

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.

-> Follow me on Medium & LinkedIn
-> Find more blogs @https://asyncq.com/

-> For the latest data engineering articles subscribe to Data Engineering Bulletin

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Suraj Mishra

Suraj Mishra

319 Followers

Google Cloud Certified Professional Data Engineer | Backend Engineering @ Rakuten Card | Blogs @https://asyncq.com/