Subtract quantity from some entry in base of the DATE in MySql database

Question

I have the following table.

CREATE TABLE ARTICOLISCADENZA(CODARTICOLO int NOT NULL,
QUANTITA INT,
DATA DATE,
LOTTO VARCHAR(200),
FOREIGN KEY(CODARTICOLO) REFERENCES ARTICOLIDETT(CODARTICOLO)
ON DELETE CASCADE
ON UPDATE CASCADE
);

Now I have these two rows:

Now I need to build a query to substract the quantity in base of the date started from the oldest date to newest date.

For example I should to remove 5 quantity from this table where CodArticolo = 1503, I should to remove the first 4 quantity from the entry where DATA = '2025-01-10' and the 1 quantity where CodArticolo = 1503 and DATA = '2025-02-10'.

How can I do to make this using a query

Answer 1

SELECT *,
       CASE WHEN @amount >= SUM(quantity) OVER w
            THEN quantity
            ELSE GREATEST(@amount + quantity - SUM(quantity) OVER w, 0)
            END amount
FROM test
WHERE code = 1
WINDOW w AS (ORDER BY `date`)

fiddle