Introduction
- In this article will solve Highest Numbers of Products SQL question which is asked by eBay 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_transactions table that consist of orders made by eBay customers.
- We need to query to get top 3 users who have spent at-least $1000 in total.
![](https://cdn-images-1.medium.com/max/1600/1*4sO6LUBCkG0k8VtyzeonOA.png)
![](https://cdn-images-1.medium.com/max/1600/1*5FsC6OIENQQoK5uNZ2FKtQ.png)
Solution
- We first need to group all the transaction for each user and perform count of products that user purchased.
![](https://cdn-images-1.medium.com/max/1600/1*JpW70i1n78DB8kyQl7ZMiw.png)
![](https://cdn-images-1.medium.com/max/1600/1*WWPmF_AVNUXu0Wo4WWC0uA.png)
- Once we have number of product purchased by each user , we can filter based amount of money user spend . we can do that using Having clause doing sum(spend).
![](https://cdn-images-1.medium.com/max/1600/1*yIQOtEBwIi1SBiPUv3C1XA.png)
- Now all we have to filter top 3 records and we have to sort the records based on count of product and total spend.
![](https://cdn-images-1.medium.com/max/1600/1*A4i2ZbAViz0FZCuqOEB6jw.png)
- Here is the the final output.
![](https://cdn-images-1.medium.com/max/1600/1*pEkqEBPEfsWrxs6g5q2L5w.png)
Final Solution
select user_id, count(product_id) as product_num from user_transactions
group by user_id
having sum(spend) >=1000
order by product_num desc, sum(spend) desc
limit 3
Submission
- Our solution get accepted by the platform.
![](https://cdn-images-1.medium.com/max/1600/1*Srh7zb8Vyy7NGOoCg0mlDQ.png)
Conclusion
- In this article, we solved question asked by eBay. In this particular question we learnt how to use having clause and apply sorted order many columns.
Before You Leave
- Upskill your SQL skills with analyst builder. (Use discount code: ABNEW20OFF for 20% off).
- Crack SQL Interview With Grokking the SQL Interview [Free Sample Copy]