Top online order locations by revenue
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 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.