There is a table with the prices of goods from different sellers for different dates. The same item can be sold by different sellers. There is only one entry per date for one item for one seller.
Some dates may be missing for all items
| id_seller | id_item | price | sale_date |
|---|---|---|---|
| 1 | 1 | 10 | 2022-07-08 |
| 2 | 1 | 11 | 2022-07-08 |
| 1 | 2 | 20 | 2022-07-08 |
| 2 | 2 | 19 | 2022-07-08 |
| 1 | 1 | 12 | 2022-07-11 |
| 2 | 1 | 13 | 2022-07-11 |
| 1 | 2 | 22 | 2022-07-11 |
| 2 | 2 | 21 | 2022-07-11 |
As you can see, this table has no rows for 2022-07-09 and 2022-07-10.
I need to get the average price of an item for all sellers each of the 7 days back from the specified date, including missing dates (for these days in the column price will be NULL).
The result I need should look like this
| id_item | avg_price | sale_date |
|---|---|---|
| 1 | 10.5 | 2022-07-08 |
| 2 | 19.5 | 2022-07-08 |
| 1 | NULL | 2022-07-09 |
| 2 | NULL | 2022-07-09 |
| 1 | NULL | 2022-07-10 |
| 2 | NULL | 2022-07-10 |
| 1 | 12.5 | 2022-07-11 |
| 2 | 21.5 | 2022-07-11 |
Can it be done by SQL query in MySQL 8.0 without using stored procedures ?
