By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE prices (
id INT,
product_id INT, /*Foreign key*/
created_at DATE,
price INT
);
INSERT INTO prices (id, product_id, created_at, price) VALUES (1, 1, '2020-01-01', 11000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (2, 2, '2020-01-01', 3999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (3, 3, '2020-01-01', 9999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (4, 4, '2020-01-01', 2000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (5, 1, '2020-01-02', 9999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (6, 2, '2020-01-02', 2999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (7, 5, '2020-01-02', 2999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (8, 7, '2020-01-03', 8999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (9, 1, '2020-01-03', 7000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (10, 5, '2020-01-03', 4000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (11, 6, '2020-01-03', 3999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (12, 3, '2020-01-03', 6999);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select *
from prices
order by 2, 3
| id | product_id | created_at | price |
|---|---|---|---|
| 1 | 1 | 2020-01-01 | 11000 |
| 5 | 1 | 2020-01-02 | 9999 |
| 9 | 1 | 2020-01-03 | 7000 |
| 2 | 2 | 2020-01-01 | 3999 |
| 6 | 2 | 2020-01-02 | 2999 |
| 3 | 3 | 2020-01-01 | 9999 |
| 12 | 3 | 2020-01-03 | 6999 |
| 4 | 4 | 2020-01-01 | 2000 |
| 7 | 5 | 2020-01-02 | 2999 |
| 10 | 5 | 2020-01-03 | 4000 |
| 11 | 6 | 2020-01-03 | 3999 |
| 8 | 7 | 2020-01-03 | 8999 |
select created_at, avg(prev_price - price), sum(prev_price - price)
from (select p.*, lag(price) over (partition by product_id order by created_at) as prev_price
from prices p
) p
group by created_at
order by created_at
| created_at | avg | sum |
|---|---|---|
| 2020-01-01 | null | null |
| 2020-01-02 | 1000.5000000000000000 | 2001 |
| 2020-01-03 | 1666.0000000000000000 | 4998 |