Data Interview Qs - Top online order locations by revenue

Top online order locations by revenue

SQL Database Querying Window Function Subquery

Forward this email to a friend or colleague and challenge them to solve it.

Hi,

Suppose you're given the following tables called 'orders' and 'order_info'. The table 'orders' shows revenue values for unique orders along with the associated channel ('online' or 'in_store') while the table 'order_info' shows the order's ID along with its location.

Table: orders

order_id	channel	date	month	revenue
1	online	2020-09-01 00:00:00	9	100
2	online	2020-09-03 00:00:00	9	125
3	in_store	2020-10-11 00:00:00	10	208
4	in_store	2020-08-21 00:00:00	8	80
5	online	2020-08-13 00:00:00	8	200
6	online	2020-08-16 00:00:00	8	210
7	in_store	2020-08-16 00:00:00	8	205
8	online	2020-10-11 00:00:00	10	215
9	online	2020-08-16 00:00:00	8	203
10	in_store	2020-09-01 00:00:00	9	400
11	online	2020-08-01 00:00:00	8	107

Table: order_info

order_id	location
1	NYC
2	NYC
3	LAX
4	LAX
5	SEA
6	AUS
7	LON
8	LAX
9	BLD
10	SEA
11	AUS

Using these tables, write a SQL query to return the top 3 'online' orders and their associated locations based on revenue generated. You can assume that each order has a unique revenue value, but you should be able to highlight the implications of ties in revenue values and how you would handle that.

Click here to view these tables in an interactive SQL fiddle.

You’ve accumulated 273 questions since joining our email list. Create a premium account to access detailed solutions to each problem (including this one) + our new Case studies.

Was this email forwarded to you? You can join our list here to receive questions