Find first uncovered debt month in Oracle Sql

Question

I want to find first month in which the debt is not fully covered. I will try to explain:

A subscriber took 10$ debt in July and paid 5$, In August took 5$ and paid 5$, In September took 5$ and paid 2$, In October took 2$ and didn't paid. If the debt is not paid in the current month, then the previous debts should be paid with the next month's payments. So, July's debt fully covered in august and August's debt not fully covered. So, August must be "first uncover month". How can I write script in oracle SQL to find "first uncover month"?

this is table:

CREATE TABLE debt_payments (
    month VARCHAR2(10),
    debt_taken NUMBER,
    payment NUMBER
);

INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('July', 10, 5);
INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('August', 5, 5);
INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('September', 5, 2);
INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('October', 2, 0);

I wrote this but didn't work real base and only find first month when remaining_debt > 0.

WITH debt_summary AS (
    SELECT 
        month,
        SUM(debt_taken) OVER (ORDER BY month) AS total_debt,
        SUM(payment) OVER (ORDER BY month) AS total_payment
    FROM 
        debt_payments
),
remaining_debt AS (
    SELECT 
        month,
        total_debt - total_payment AS remaining_debt
    FROM 
        debt_summary
)
SELECT 
    month
FROM 
    remaining_debt
WHERE 
    remaining_debt > 0
ORDER BY 
    month
FETCH FIRST ROW ONLY;  -- Get the first month where debt is uncovered

Answer 1

One of the options could be to use MODEL clause which alows you to address and manipulate sql data pretty much like the data in Excel sheet.

--      S a m p l e    D a t a :
CREATE TABLE debt_payments ( month DATE, debt_taken NUMBER, payment NUMBER );
INSERT INTO debt_payments 
  Select DATE '2024-07-01', 10, 5 From Dual Union All
  Select DATE '2024-08-01',  5, 5 From Dual Union All
  Select DATE '2024-09-01',  5, 2 From Dual Union All
  Select DATE '2024-10-01',  2, 0 From Dual;

... assuming that there is one row of data per month ...

--      S Q L : 
SELECT   month, debt_taken, payment, balance
FROM    ( Select     month, debt_taken, payment, Row_Number() Over(Order By month) as rn 
          From       debt_payments
        )
  MODEL    Dimension By ( rn )
           Measures  ( month, debt_taken, payment, 0.00 as next_month_payment, 0.00 balance )
  RULES ( next_month_payment[ANY] = Nvl(CASE WHEN month[CV() + 1] Is Null
                                            THEN 0
                                            WHEN  debt_taken[CV()] - 
                                                  Nvl(Sum(next_month_payment)[rn = CV() - 1], 0) -
                                                  payment[CV()] >= payment[CV() + 1] 
                                            THEN payment[CV() + 1]
                                       ELSE debt_taken[CV()] - 
                                            Nvl(Sum(next_month_payment)[rn = CV() - 1], 0) -
                                            payment[CV()]
                                       END, 0), 
         balance[ANY] = ( debt_taken[CV()] - (payment[CV()] + next_month_payment[CV()]) ) -
                         CASE WHEN payment[CV()] + next_month_payment[CV()] = 0
                              THEN (  ( payment[CV()] - Nvl(next_month_payment[CV() - 1], 0) )   )
                         ELSE 0
                         END
       )

/*      R e s u l t : 
MONTH      DEBT_TAKEN  PAYMENT  BALANCE
---------  ----------  -------  -------
01-JUL-24          10        5        0
01-AUG-24           5        5        5
01-SEP-24           5        2        3
01-OCT-24           2        0        2      */

... select the row with the first month having debt not fully payed (August, balance = 5) ...
See the fiddle here.

More about the MODEL CLAUSE here.