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 logistics (
id SERIAL PRIMARY KEY,
time_stamp DATE,
product VARCHAR(255),
quantity INT
);
INSERT INTO logistics
(time_stamp, product, quantity)
VALUES
('2020-01-14', 'Product_A', '100'),
('2020-01-14', 'Product_B', '300'),
('2020-01-15', 'Product_B', '400'),
('2020-01-15', 'Product_C', '350'),
('2020-01-16', 'Product_B', '530'),
('2020-01-16', 'Product_C', '250'),
('2020-01-16', 'Product_D', '670'),
('2020-01-17', 'Product_C', '380'),
('2020-01-17', 'Product_D', '980'),
('2020-01-17', 'Product_E', '700'),
('2020-01-17', 'Product_F', '450');
11 rows affected
SELECT
t1.time_stamp AS time_stamp,
t1.product AS product,
SUM(t1.difference) AS difference
FROM
(SELECT
l.time_stamp AS time_stamp,
l.product AS product,
Coalesce(l.quantity-LAG(l.quantity) OVER (Partition by l.product ORDER BY l.product, l.time_stamp), l.quantity) AS difference
FROM logistics l
ORDER BY 1,2) t1
WHERE t1.difference < 0
GROUP BY 1,2
ORDER BY 1,2;
| time_stamp | product | difference |
|---|---|---|
| 2020-01-16 | Product_C | -100 |