SQL to Determine Unpaid Balance Within Periods

Question

I have the following table:

/**
| NAME  | DELTA (PAID - EXPECTED) | PERIOD |
|-------|-------------------------|--------|
| SMITH |                      -50|       1|
| SMITH |                        0|       2|
| SMITH |                      150|       3|
| SMITH |                     -200|       4|
| DOE   |                      300|       1|
| DOE   |                        0|       2|
| DOE   |                     -200|       3|
| DOE   |                     -200|       4|
**/
DROP TABLE delete_me;

CREATE TABLE delete_me (
    "NAME" varchar(255),
    "DELTA (PAID - EXPECTED)" numeric(15,2),
    "PERIOD" Int
);

INSERT INTO delete_me("NAME",   "DELTA (PAID - EXPECTED)", "PERIOD")
VALUES
  ('SMITH',  -50,   1),
  ('SMITH',    0,   2),
  ('SMITH',  150,   3),
  ('SMITH', -200,   4),
  ('DOE',    300,   1),
  ('DOE',      0,   2),
  ('DOE',   -200,   3),
  ('DOE',   -200,   4)

Where period represents time, with 1 being the newest and 4 being the oldest. In each time period the person was charged and amount and they could pay off that amount or more. A negative delta means that they owe for that time period. A positive delta means that they paid over the expected amount and has a credit for that time period that can be applied to other time periods. If there's a credit we'd want to pay off the oldest time period first. I want to get how much unpaid debt is still looming for each time period.

So in the example above I'd want to see:

| NAME  | DELTA (PAID - EXPECTED) | PERIOD | PERIOD BALANCE |
|-------|-------------------------|--------|----------------|
| SMITH |                      -50|       1|             -50|
| SMITH |                        0|       2|               0|
| SMITH |                      150|       3|               0|
| SMITH |                     -200|       4|             -50|
| DOE   |                      300|       1|               0|
| DOE   |                        0|       2|               0|
| DOE   |                     -200|       3|            -100|
| DOE   |                     -200|       4|               0|

How can I use Postgres SQL to show the Unpaid debt within periods?

Additional description: for doe initially, in the oldest period, 200 was owed, the next period the owed the original 200 plus another 200 (400 total owed). In Period 2 the monthly charge was paid, but not the past balances. In the most recent period (1) 300 over the monthly amount was paid (200 of this was applied to the oldest debt in period 4, meaning it was paid off; leaving 100 to apply to period three's debt; and after applying the remaining 100, 100 was still owed).

For the Smith family initially in period 4 they underpaid 200. The next period they overpaid 150 for the month and this was applied to the oldest debt of 200, leaving 50 to still be paid. In period 2 the monthly bill was paid exactly, they still owed the 50 dollars from period 4. Then in period 1 they underpaid 50. They owe 100 in total, 50 for period 1 and 50 for period 4.

user_7326738 · Answer

The query below utilizes a recursive cte with several JSON structures. The usage of the latter allows accurate tracking of negative to positive balance intervals with possibly more than one potential positive balances after negative:

with recursive cte(n, l, p, js, js1) as (
   select d1.name, d5.delta, d1.m, jsonb_build_object(d1.m, d5.delta), jsonb_build_array(d1.m) 
   from (select d.name, max(d.period) m from delete_me d where d.delta < 0 group by d.name) d1 
   join delete_me d5 on d5.name = d1.name and d5.period = d1.m
   union all
   select c.n, d.delta, d.period, 
      case when d.delta < 0 then c.js||jsonb_build_object(d.period, d.delta) 
           when d.delta = 0 then c.js 
           else (select jsonb_object_agg(k.v3, least((c.js -> k.v3::text)::int + 
                 greatest(d.delta + coalesce((select sum((c.js -> v2.value::text)::int) 
                      from jsonb_array_elements(c.js1) v2 where  v2.value::int > k.v3),0),0),0)) 
                 from (select v.value::int v3 from jsonb_array_elements(c.js1) v 
                      order by v.value::int desc) k)||jsonb_build_object(d.period, greatest(d.delta + (select sum((c.js -> v2.value::text)::int) 
                            from jsonb_array_elements(c.js1) v2),0)) end, 
      case when d.delta < 0 then (case when c.l <= 0 then c.js1 else '[]'::jsonb end) || ('['||d.period||']')::jsonb 
           else c.js1 end 
    from cte c join delete_me d on d.period = c.p - 1 and d.name = c.n
)
select d.*, coalesce((c.js -> d.period::text)::int, 0) from delete_me d 
join cte c on c.n = d.name where c.p = 1
order by d.name desc, d.period asc

user_12705912 · Answer

According to what I understood, you want to distribute the sum of positive DELTA values (credit) among the negative DELTA values starting from the oldest period.

with cte as (
  Select Name_, DELTA, PERIOD_,
  Sum(case when DELTA<0 then delta else 0 end)
  Over (Partition By NAME_ Order By PERIOD_ desc) +
  Sum(case when DELTA>0 then delta else 0 end) 
  Over (Partition By NAME_) as positive_credit_to_negativ_delta
  From delete_me
)

Select Name_,DELTA,PERIOD_,positive_credit_to_negativ_delta,
case 
    when delta >= 0 then 0 
    else
    case
        when positive_credit_to_negativ_delta >= 0 then 0
        else
        greatest(delta , positive_credit_to_negativ_delta)
    end
end as PERIOD_BALANCE
from cte
Order By NAME_,PERIOD_

See a demo from db-fiddle.

The idea in this query is to find the sum of all positive DELTA values for each user, then add that sum to the cumulative sum of the negative values starting from the oldest period. The result of this addition is stored in positive_credit_to_negativ_delta in the query.

Of course for DELTA with values >= 0, the result will be 0 since no debit for that period.

For negative DELTA values:

If the value of positive_credit_to_negativ_delta is >= 0 then the result will be 0, that means the period delta is covered by the positive credit.
If the value of positive_credit_to_negativ_delta is < 0 then the result will be the max value from positive_credit_to_negativ_delta and DELTA.

SQL to Determine Unpaid Balance Within Periods

2 Answers