Cities With Completed Trades — Robinhood SQL Question

  • Post last modified:September 6, 2022
  • Reading time:2 mins read

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.

Leave a Reply