Avg. sales by location in SQL
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 number of sales as well as the average sale price (in dollars) for a given location.
Your output should return the following columns:
location | number_sales | avg_sale_price |
---|---|---|
X | Y | Z |
A | B | C |