21 Cities With Completed Trades Robinhood SQL Interview Question
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.
trades Table:
Column Name Type
order_id integer
user_id integer
price decimal
quantity integer
status string('Completed' ,'Cancelled')
timestamp datetime
trades Example Input:
order_id user_id price quantity status timestamp
100101 111 9.80 10 Cancelled 08/17/2022 12:00:00
100102 111 10.00 10 Completed 08/17/2022 12:00:00
100259 148 5.10 35 Completed 08/25/2022 12:00:00
100264 148 4.80 40 Completed 08/26/2022 12:00:00
100305 300 10.00 15 Completed 09/05/2022 12:00:00
100400 178 9.90 15 Completed 09/09/2022 12:00:00
100565 265 25.60 5 Completed 12/19/2022 12:00:00
users Table:
Column Name Type
user_id integer
city string
email string
signup_date datetime
users Example Input:
user_id city email signup_date
111 San Francisco [email protected] 08/03/2021 12:00:00
148 Boston [email protected] 08/20/2021 12:00:00
178 San Francisco [email protected] 01/05/2022 12:00:00
265 Denver [email protected] 02/26/2022 12:00:00
300 San Francisco [email protected] 06/30/2022 12:00:00
Example Output:
city total_orders
San Francisco 3
Boston 2
Denver 1