Highest percent of revenue in store
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 location with the highest percent of their revenue in-store (as denoted by the 'in_store' label in the 'channel' field. If there is a tie, return the location with the higher total in-store revenue.
Click here to view these tables in an interactive SQL fiddle.