Question: Assume you are given the below tables for trades and users. Write a query to return the list of the top 3 cities with the highest number of completed order fractions.
Difficulty Level: Easy, Source: NickSingh.com, Tag: Robinhood SQL Interview Question
Note: completed order fraction = total completed orders / total numbers of orders placed
Assumptions: No NULLS present.
Approach:
Basic Explanation: A given trade could be either buy or sell and the status could be either complete or cancelled (See the trades table), the question asks for fraction of ‘completed’ trades.
Step-1: Understand that the question asks for information on city level, which means aggregation has to be done on cities. Also, the common link between the users and the trades table is user_id which will be used for joining these two tables.
Step-2: After joining these two tables, for each city just calculate the total processed trades and total completed trades and we are good to go.
Solution:
select t2.city, sum(case when t1.status = 'complete' then 1 else 0 end) as total_completed, sum(case when t1.status = 'complete' then 1 else 0 end)/count(t1.order_id) as complete_frac from trades t1 join users t2 on t1.user_id = t2.user_id group by t2.city order by complete_frac DESC limit 3