logo

Back to questions

Cities With Completed Trades

Easy

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

You are given the tables below containing information on Robinhood trades and users. Write a query to list the top three cities that have the most completed trade orders in descending order.

Output the city and number of orders.

Table:

Column NameType
order_idinteger
user_idinteger
pricedecimal
quantityinteger
statusstring('Completed' ,'Cancelled')
timestampdatetime

Example Input:

order_iduser_idpricequantitystatustimestamp
1001011119.8010Cancelled08/17/2022 12:00:00
10010211110.0010Completed08/17/2022 12:00:00
1002591485.1035Completed08/25/2022 12:00:00
1002641484.8040Completed08/26/2022 12:00:00
10030530010.0015Completed09/05/2022 12:00:00
1004001789.9015Completed09/09/2022 12:00:00
10056526525.605Completed12/19/2022 12:00:00

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
265Denvershadower_@hotmail.com02/26/2022 12:00:00
300San Franciscohoustoncowboy1122@hotmail.com06/30/2022 12:00:00

Example Output:

citytotal_orders
San Francisco3
Boston2
Denver1

Explanation

In this example, San Francisco has first place with 3 orders, Boston has second place with 2 orders, and Denver has third place with 1 order.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.