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 q2.time_stamp, q2.product, q2.difference
FROM (
SELECT q1.*
, MAX(CASE WHEN q1.quantity < q1.prev_quantity THEN 1 ELSE 0 END)
OVER (PARTITION BY q1.product) AS has_difference
, (q1.quantity - coalesce(q1.prev_quantity, 0)) AS difference
FROM (
SELECT l.product, l.time_stamp, l.quantity
, LAG(l.quantity) OVER (PARTITION BY l.product ORDER BY l.time_stamp) AS prev_quantity
FROM logistics l
) AS q1
) q2
WHERE q2.has_difference = 1
ORDER BY q2.product, q2.time_stamp;
| time_stamp | product | difference |
|---|---|---|
| 2020-01-15 | Product_C | 350 |
| 2020-01-16 | Product_C | -100 |
| 2020-01-17 | Product_C | 130 |