Most expensive grocery items sold by location
Question
You are given the below tables, showing Store, Product, and Sales information for a chain of grocery stores. The columns are labeled in such a way that you should be able to interpret what each field is showing.
Store
| store_id | location |
|---|---|
| 91110 | New York |
| 99525 | Los Angeles |
| 37340 | Tokyo |
| 32016 | Detroit |
| 57507 | London |
Product
| product_id | product_name | price_usd |
|---|---|---|
| 31331 | Apples | 2 |
| 34611 | Lettuce | 3 |
| 49760 | Chicken | 5 |
| 26583 | Lemons | 1 |
| 20267 | Bread | 2 |
Sales
| sale_id | product_id | store_id | date |
|---|---|---|---|
| 1 | 31331 | 91110 | 02/20/2020 |
| 1 | 31331 | 91110 | 02/20/2020 |
| 2 | 34611 | 57507 | 02/20/2020 |
| 3 | 26583 | 37340 | 02/20/2020 |
| 3 | 34611 | 32016 | 02/20/2020 |
| 3 | 20267 | 99525 | 02/21/2020 |
| 4 | 31331 | 99525 | 02/21/2020 |
| 5 | 49760 | 99525 | 02/21/2020 |
| 6 | 34611 | 57507 | 02/21/2020 |
| 7 | 31331 | 91110 | 02/21/2020 |
Using the tables above, write a SQL query to return the most expensive product shown in each location.
Your output should return the following columns:
| location | product_name | price_usd |
|---|---|---|
| X | Y | Z |
| A | B | C |
Where each row represents a single location and that location's most expensive product sold.