How to calculate the stock using FIFO method(MySQL8)

Question

I would like to ask how to use the FIFO method with MYSQL8 to generate the expected result as follow. The date of the stock in the result table must be the stock in date. Thanks to everyone if you can help.

Orginal_Table

Stock Name	In/out	Quantity	Date
Apple	in	10	1/1/2021
Banana	in	5	1/2/2021
Banana	out	3	1/5/2021
Banana	in	4	1/6/2021
Cherry	in	3	1/6/2021
Cherry	in	4	1/7/2021
Cherry	out	5	1/8/2021

Expected_result

Stock Name	balance	stock_in_date
Apple	10	1/1/2021
Banana	2	1/2/2021
Banana	4	1/6/2021
Cherry	2	1/7/2021

Answer 1

WITH 
cte1 AS (
    SELECT name, SUM(quantity * (operation = 'out')) went_out
    FROM test
    GROUP BY name
),
cte2 AS (
    SELECT *, SUM(quantity * (operation = 'in')) OVER (PARTITION BY name ORDER BY operation_date) amount
    FROM test
)
SELECT name, 
       operation_date,
       CASE WHEN amount - quantity < went_out 
            THEN amount - went_out
            ELSE quantity
            END result
FROM cte1
JOIN cte2 USING (name)
WHERE operation = 'in'
  AND went_out < amount
ORDER BY 1,2;

fiddle with query building steps.