Back to questions

# Cities With Completed Trades [Robinhood SQL Interview Question]

Easy

This is the same question as problem #2 in the SQL Chapter of Ace the Data Science Interview!

Assume you're given the tables containing completed trade orders and user details in a Robinhood trading system.

Write a query to retrieve the top three cities that have the highest number of completed trade orders listed in descending order. Output the city name and the corresponding number of completed trade orders.

### Table:

Column NameType
order_idinteger
user_idinteger
quantityinteger
statusstring ('Completed', 'Cancelled')
datetimestamp
pricedecimal (5, 2)

### Example Input:

order_iduser_idquantitystatusdateprice
10010111110Cancelled08/17/2022 12:00:009.80
10010211110Completed08/17/2022 12:00:0010.00
10025914835Completed08/25/2022 12:00:005.10
10026414840Completed08/26/2022 12:00:004.80
10030530015Completed09/05/2022 12:00:0010.00
10040017832Completed09/17/2022 12:00:0012.00
1005652652Completed09/27/2022 12:00:008.70

### Table:

Column NameType
user_idinteger
citystring
emailstring
signup_datedatetime

### Example Input:

user_idcityemailsignup_date
111San Franciscorrok10@gmail.com08/03/2021 12:00:00
148Bostonsailor9820@gmail.com08/20/2021 12:00:00
178San Franciscoharrypotterfan182@gmail.com01/05/2022 12:00:00
300San Franciscohoustoncowboy1122@hotmail.com06/30/2022 12:00:00

### Example Output:

citytotal_orders
San Francisco3
Boston2
Denver1

In the given dataset, San Francisco has the highest number of completed trade orders with 3 orders. Boston holds the second position with 2 orders, and Denver ranks third with 1 order.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14