Total revenue by location
Question
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 total revenue by location, sorted by revenue in descending order. 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.