Find the matched rows based on redeem value in MySQL

I am implementing the cashback functionality with expiry feature. I am trying to redeem the partial amount based on early expiry date. I’ve already ordered the rows based on expiry date with the following mysql command.

SELECT * FROM `cashback` WHERE `user_id` = 1 and `used`= 'NO' AND IF(CONCAT(`point_expiry`) !='0000-00-00 00:00:00', `point_expiry` >= NOW(), NOW()) ORDER BY (case when CONCAT(`point_expiry`) = '0000-00-00 00:00:00' then 9999
    else 1
    end) ASC, `point_expiry` ASC

And the output for the following will be

Now i want to redeem the value based on the above query result

Let say i want to redeem 35$ for the above result and the expected result will be

Here used_amount column represent the specific redeem value($35) redeemed based on amount column

Much appreciate your help!

>Solution :

This uses SUM(amount) OVER(ORDER BY ...) to calculate a running total and compares it to the balance –

SELECT *
FROM (
    SELECT
        `id`,
        `amount`,
        `point_expiry`,
        `used`,
        `amount` - GREATEST(SUM(`amount`) OVER (ORDER BY IF(`point_expiry` = '0000-00-00 00:00:00', 1, 0) ASC, `point_expiry` ASC, id ASC) - /* Here is your amount --> */ 35, 0) AS `used_amount`
    FROM `cashback`
    WHERE (`point_expiry` >= NOW() OR `point_expiry` = '0000-00-00 00:00:00')
    AND `used` = 'NO'
    AND `user_id` = 1
) t
WHERE `used_amount` > 0;