Cities w/ Most Trades
ASKED IN INTERVIEWRobinhood
Problem Statement
Robinhood is analyzing regional trading activity. You need to identify the top 3 cities that have the highest number of completed stock trades.
Write an SQL query to retrieve the city name and the total count of completed trades for those top 3 cities.
Rules:
- Only count trades where the status is Completed.
- Output columns: city, total_trades.
- Results must be sorted by total_trades in descending order.
- In case of a tie in trade counts, sort the cities alphabetically in ascending order.
Tests your understanding of
Aggregation, JOIN and Sorting
Input Tables
trades
| user_id(INTEGER) | status(VARCHAR) |
|---|---|
| 101 | Completed |
| 101 | Completed |
| 102 | Completed |
| 104 | Completed |
| 105 | Completed |
users
| user_id(INTEGER) | city(VARCHAR) |
|---|---|
| 101 | San Francisco |
| 102 | New York |
| 104 | New York |
| 105 | Austin |
Expected Output
| city(VARCHAR) | total_trades(INTEGER) |
|---|---|
| New York | 2 |
| San Francisco | 2 |
| Austin | 1 |
Tags
EasyASKED IN INTERVIEWAggregationJOINSorting
10-15 min
58%