Introduction
- In this article we will solve Cities with completed trades question which is asked by robinhood 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 robinhood trades table that mainly consist of trade status along with other trade info such as price, quantity.
- We have also been given user table that provides user information along with location.
- Our objective is to provide output that consist of cities where maximum number of trades were made ( completed status ) in descending order.
Solution
- If we look at trades input , we will know right away that we need to count of incident where trades status is completed.
- But we need to count this number as per each city, but trades doesn’t provide users city info, so we have to join user info with trades.
- We will perform left join, so that each trades will have user_city info.
select u.city, count(t.status) total_trades from
trades t join users u on t.user_id = u.user_id
where t.status='Completed'
- Now if we perform count operation we can group it by city info to get the required output.
- we also need to order the result by number of orders in descending and filter only top 3 cities.
select u.city, count(t.status) total_trades from
trades t join users u on t.user_id = u.user_id
where t.status='Completed'
group by u.city
order by total_trades desc
limit 3
- So thats the solution for cities with completed trades.
Submission
- Our solution get’s accepted by the platform successfully.
- do try out this problems and many more for free if you want to practice @datalemur
Conclusion
- In this SQL problem we solve cities with trades using left join and simple count aggregation.
- This problem is pretty basic and can be solved if you have basic knowledge of SQL.
Bonus Tip
- If you want to upskill your SQL interview game, you can definitely check out this bestseller course