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'),
('2020-01-18', 'Product_E', '900'),
('2020-01-18', 'Product_F', '200');
13 rows affected
WITH cte AS
(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)
SELECT
t1.time_stamp AS time_stamp,
t1.product AS product,
SUM(t1.difference) AS difference
FROM cte t1
WHERE EXISTS
(SELECT
t2.product AS product
FROM cte t2
WHERE t2.difference < 0
AND t2.product = t1.product
GROUP BY 1
ORDER BY 1)
GROUP BY 1,2
ORDER BY 1,2;
| time_stamp | product | difference |
|---|---|---|
| 2020-01-15 | Product_C | 350 |
| 2020-01-16 | Product_C | -100 |
| 2020-01-17 | Product_C | 130 |
| 2020-01-17 | Product_F | 450 |
| 2020-01-18 | Product_F | -250 |