Calculate total difference of values between two timestamps

DB-Fiddle

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', '350'),
('2020-01-16', 'Product_D', '670'),

('2020-01-17', 'Product_C', '500'),
('2020-01-17', 'Product_D', '980'),
('2020-01-17', 'Product_E', '700'),
('2020-01-17', 'Product_F', '450');

Expected Result:

time_stamp  |   difference   |           info
------------|----------------|--------------------------------------
2020-01-14  |       400      |       =(100+300)
2020-01-15  |       350      |       =(400+350)-(300+100)
2020-01-16  |       800      |       =(530+350+670)-(400+350)
2020-01-17  |      1080      |       =(500+980+700+450)-(530+350+670)

I want to calculate the total difference of the quantity between two timestamps.
Therefore, I tried to use the query from this question:

SELECT
t1.time_stamp AS time_stamp,
t1.quantity AS quantity,
COALESCE(t1.quantity-LAG(t1.quantity) OVER (PARTITION BY t1.time_stamp ORDER BY t1.time_stamp), t1.quantity) AS difference
FROM 

  (SELECT
  l.time_stamp AS time_stamp, 
  SUM(l.quantity) AS quantity
  FROM logistics l
  GROUP BY 1
  ORDER BY 1) t1

GROUP BY 1,2
ORDER BY 1,2;

However, I am not able to get the expected result.
What do I need to change to make it work?

Solution 1:

The partition by clause in the lag call of your query is redundant. The inner query is already grouped by the timestamp, so the outer query needn't (and shouldn't!) further partition the results.
With a partition by clause, each lag call is applied to just one timestamp (since they are already unique in the outer query), and you get the wrong result.

Remove it, and you should be fine:

SELECT
t1.time_stamp AS time_stamp,
t1.quantity AS quantity,
COALESCE(t1.quantity-LAG(t1.quantity) OVER (ORDER BY t1.time_stamp), t1.quantity) AS difference
FROM 

  (SELECT
  l.time_stamp AS time_stamp, 
  SUM(l.quantity) AS quantity
  FROM logistics l
  GROUP BY 1
  ORDER BY 1) t1

GROUP BY 1,2
ORDER BY 1,2;

DBFiddle demo

Solution 2:

You can remove the PARTITION BY in the LAG function.

If the "product" column was part of the result. Then PARTITION BY product would make sense.
But since that's not the case, the ORDER BY t1.time_stamp is sufficient.

SELECT
t1.time_stamp AS time_stamp,
t1.quantity AS quantity,
COALESCE(t1.quantity-LAG(t1.quantity) OVER (ORDER BY t1.time_stamp), t1.quantity)
AS difference
FROM 

  (SELECT
  l.time_stamp AS time_stamp, 
  SUM(l.quantity) AS quantity
  FROM logistics l
  GROUP BY 1
  ORDER BY 1) t1

GROUP BY 1,2
ORDER BY 1,2;

time_stamp | quantity | difference
:--------- | -------: | ---------:
2020-01-14 |      400 |        400
2020-01-15 |      750 |        350
2020-01-16 |     1550 |        800
2020-01-17 |     2630 |       1080

db<>fiddle here