How to sum two column row elements in extra column?

I'm FULL OUTER JOIN two tables Givens in db<>fiddle. The first table name is product_purchase and the second table name is sales_return. They are as follows:

SELECT *FROM 'product_purchase'

date	invoice_no	product_id	purchase_quantity	price
2021-01-01 10:00:00	p-101	A-1	100	100
2021-01-02 11:00:00	p-102	A-1	90	90
2021-01-03 12:00:00	p-103	A-1	200	200
2021-01-04 13:00:00	p-104	A-1	250	250

SELECT *FROM 'sales_return'

date	invoice_no	product_id	sales_return_quantity	price
2021-01-01 10:00:00	r-101	A-1	10	10
2021-01-04 13:00:00	r-104	A-1	25	25

I wanted to sum product_purchase table column purchase_quantity row elements and sales_return table column sales_return_quantity row elements in extra column total. Following code Givens in db<>fiddle, I write to do this.

SELECT pp.`date`, pp.`invoice_no`, pp.`product_id`, pp.`purchase_quantity`, sr.`sales_return_quantity`, sum(pp.`purchase_quantity`) OVER (ORDER BY pp.date) AS total
FROM  product_purchase pp
       LEFT JOIN sales_return sr
          ON pp.product_id = sr.product_id AND pp.product_id != sr.product_id
UNION ALL
SELECT sr.`date`, sr.`invoice_no`, sr.`product_id`, pp.`purchase_quantity`, sr.`sales_return_quantity`, sum(sr.`sales_return_quantity`) OVER (ORDER BY sr.date) AS total
FROM   sales_return sr
       LEFT JOIN product_purchase pp
         ON pp.product_id = sr.product_id AND pp.product_id != sr.product_id
WHERE  pp.product_id IS NULL
ORDER BY pp.`date`

It sum total column table-wise separately:

total
100
10
190
390
640
35

It need the following result:

total
100
110
200
400
650
675

Solution

Instead of a FULL join use UNION ALL and SUM() window function:

SELECT date, invoice_no, product_id, purchase_quantity, sales_return_quantity,
       SUM(COALESCE(purchase_quantity, 0) + COALESCE(sales_return_quantity, 0))
       OVER (ORDER BY date, sales_return_quantity IS NOT NULL) total
FROM (
  SELECT date, invoice_no, product_id, purchase_quantity, null sales_return_quantity
  FROM product_purchase
  UNION ALL
  SELECT date, invoice_no, product_id, null purchase_quantity, sales_return_quantity
  FROM sales_return
)
ORDER BY date, sales_return_quantity IS NOT NULL

See the demo.