Calculating money amount used based on correct date sequence

Question

I have a 'transactions' table that contains users money transaction information. Money can be credited by adding money into your wallet by personal means SOURCE: PERSONAL, or by receiving a promotion, SOURCE: PROMOTION.

Whenever money gets used, it always gets deducted from PROMOTION first, then it gets deducted from PERSONAL. I only have a column that shows when personal money gets used, not when promotion gets used. Based on the table, I have I'm trying to calculate a column that gives the amount that was used from promotion source only.

In the picture below, this member used personal money to add in their wallet ('40'), then he received +60 and +15 the next days coming from promotion. Then on Dec.26th, he spent 10. Since money always gets deducted from promotion first based on DATE order, I know that PROMOTION_USED for the promotion record received on Dec.24th will be $10. Since 10 gets deducted from 60. This means this record has $50 left in promotion value.

So the result will look this like:

If the member keeps making payments, it will always take away from the promotion records first, then take from the personal. If at anytime, personal money gets used, it will appear in the 'PERSONAL_USED' column. This column we have.

In the example below, the user made more payments.

He then made additional payments of $20 and $40 on different occasions. Since, we have $50 dollars left in the record for Dec.24th, this will get updated to $60 (meaning he used all his promotion money from this record). This covers all of the payments made on Dec 27th ($20) and $30 out of the $40 payment made on Dec 28th. That means that $10 from the payment made on Dec.28th gets covered by the promotion received on Dec.25th. So his column for PROMOTION_USED on the Dec.25h record will be $10.

The PROMOTION_USED column will look like this:

This means the user has $5 left of his promotion money, and if he makes more payment greater than $5 the difference will come from his PERSONAL money.

Is there away to formulate a query to calculate promotion_used to be deducted from the promotion money received based on date order?

Answer 1

So for this example, it's time to bust out a User Define Tabular Function:

CREATE OR REPLACE FUNCTION carry_forward (source varchar(10), type varchar(10), amount float )
  RETURNS TABLE ( PRO_BAL FLOAT, PER_BAL FLOAT, PRO_USED float, PER_USED float )
  LANGUAGE JAVASCRIPT
    AS '{
    processRow: function (row, rowWriter, context) {
        if(row.TYPE === "received"){
            if(row.SOURCE==="promotion")
            {
                this.pro_bal += row.AMOUNT;
            }
            else if(row.SOURCE==="personal")
            {
                this.per_bal += row.AMOUNT;
            }
            
            rowWriter.writeRow({PRO_BAL: this.pro_bal, PER_BAL: this.per_bal});
        }
        else {
            // spending.
            var spend = -row.AMOUNT;
            var pro_spend = this.pro_bal > spend? spend : this.pro_bal;
            var per_spend = spend - pro_spend;
            
            this.per_bal -= per_spend;
            this.pro_bal -= pro_spend;
            
            rowWriter.writeRow({PRO_BAL: this.pro_bal, PER_BAL: this.per_bal, PRO_USED: pro_spend, PER_USED: per_spend});
        }
    },
    initialize: function(argumentInfo, context) {
     this.pro_bal = 0.0;
     this.per_bal = 0.0;
    }}';

Which we can use across our data. Two major problems, it's using floating point to do math related to money, which is a no-no. And it's no longer pure SQL, but it works really well.

WITH data(number_id, source, type, amount, date) AS (
    SELECT column1, column2, column3, column4, to_date(column5, 'YYYY-MM-DD') 
    FROM VALUES
    (111, null, 'payment', -40, '2021-12-28'),
    (111, null, 'payment', -20, '2021-12-27'),
    (111, null, 'payment', -10, '2021-12-26'),
    (111, 'promotion', 'received', 15, '2021-12-25'),
    (111, 'promotion', 'received', 60, '2021-12-24'),
    (111, 'personal', 'received', 40, '2021-12-23')
)
SELECT 
    d.number_id
    ,d.source 
    ,d.type
    ,d.date
    ,d.amount
    ,c.pro_bal
    ,c.per_bal
    ,c.pro_used
    ,c.per_used
FROM data d,
    table(carry_forward(d.source, d.type, d.amount::float) over (partition by d.number_id order by d.date)) c;
ORDER BY 1;

gives:

NUMBER_ID	SOURCE	TYPE	DATE	AMOUNT	PRO_BAL	PER_BAL	PRO_USED	PER_USED
111	personal	received	2021-12-23	40	0	40
111	promotion	received	2021-12-24	60	60	40
111	promotion	received	2021-12-25	15	75	40
111		payment	2021-12-26	-10	65	40	10	0
111		payment	2021-12-27	-20	45	40	20	0
111		payment	2021-12-28	-40	5	40	40	0