How to write off bonuses?

Question

MySQL

What is the algorithm for writing off bonuses from the client after the expiration of time.
With each purchase, the client receives n-% bonuses. the client saves saves saves. Then he spends. After 2 years, bonuses accrued at the very beginning should burn out. Help with the algorithm and structure in the database.
It is necessary somehow interesting to accrue and write off them.
Because at the moment of write-off (use), the sum of all bonuses accumulated by the client is taken and written off for the purchase. And after some time, they need to be burned in chronological order. from old to new.
PS: If, after the expiration of time, the same amount of bonus is written off as accrued (we drive the client into a minus)
It is necessary to write off the bonuses proportionally.

Answer 1

My thought:
Leave people with their miserable bonuses
and find ways to get people to spend more with these bonuses.

Answer 2

Keep records in the database about each bonus accrual:

• To calculate the number of actual points, we run through the non-expired ones (Now() < dt_void) and summarize (value_in - value_out).
  
• To write off when buying, we run from old to new, gaining the maximum number of points allowed for writing off and update value_out.
  

Answer 3

Exactly one additional field is the burn date.
When creating a bonus, immediately set the burn date.
When writing off a bonus - check the date of expiration, of course, if the bonus is overdue, then ignore it and do not use it to offset the cost of purchases.

Answer 4

My idea is
to use the burn mark. Moreover, some bonuses can work for a week, and some for several months.
number of bonuses | upload date | combustion date | id of the remote transaction.
And every day, use the crown to write off irrelevant bonuses in the form of a transaction.

Answer 5

Immediately after the write-off if (bonus < 0) bonus = 0
All.

Answer 6

With a query, you select the FIFO balance according to the dates of accrual and write off those whose date is more than a year. PostgreSQL and Oracle allow you to get such a remainder in one query.