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!

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:

Example Input:

Table:

Example Input:

Example Output:

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.

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

Let's start small by joining the and table based on the related column .

Psst, do you know why we have to join the tables? That's because the 'Completed' order is residing in the table whereas the cities are in the table.

In the SELECT statement, we pull the field from the table and the field from the table.

Output (showing the first 5 rows only):

Now that we have the table containing information on the city and orders, let's retrieve the completed orders by filtering 'Completed' orders. Note that we also want the number of orders which we can find using the function.

statement is often used with aggregate functions (i.e. , , , , ) to group the results by non-aggregate columns. Did you notice that our output is grouped by the column?

Finally, to sort the output from the highest number of completed orders, we sort it in descending order in the clause and the results to the top 3 orders.

Full solution: