Cross-referecing columns in Snowflake SQL

Question

I'm trying to build an amortization schedule using Snowflake-SQL however I need two columns to reference each other in order to calculate the active and the present value. In excel, it would be something like this:

In SQL, I tried doing it like this but it doesn't work:

,LAG(present_value) OVER (PARTITION BY ti.request_id ORDER BY ti.reference_date) AS active_value
,active_value - c.installment_amount AS present_value

How am I able to replicate what I did using excel in SQL?

Answer 1

so with a janky CTE for the seed data:

with data(y,start_date, amount, interest_y_c4, payment_c9, interest_d_c4_p1) as (
    select *
       ,(0.0007223821155291760::double) + 1.00::double
    from values
    (1,'2021-11-10', 1690.96::double, 0.263669472168149::double, 304.90::double)
), payment_days(x,days, acc_days) as (
    select *
        ,sum(column2) over(order by column1)
    from values
    (1, 0),
    (2, 28),
    (3, 35),
    (4, 28),
    (5, 24),
    (6, 31),
    (7, 30)
), rc as (

and a recursive CTE this can be solved:

with RECURSIVE rec_cte as (
    select
        d.y,
        pd.x + 1 as x,
        pd.days,
        dateadd('days',pd.acc_days, d.start_date)::date as payment_date,
        d.amount as active_value,
        0.0::double as interest,
        d.amount as present_value
    from data as d
    join payment_days as pd
        on pd.x = 1

    UNION ALL

    select 
        d.y,
        pd.x + 1 as x,
        pd.days,
        dateadd('days',pd.acc_days, d.start_date)::date as payment_date,
        round(r.present_value * pow(d.interest_d_c4_p1, pd.days),10) as _active_value,
        (_active_value - r.present_value) as interest,
        greatest(0, _active_value - d.payment_c9) as present_value

    from rec_cte as r
    join data as d
        on r.y = d.y
    join payment_days as pd
        on r.x = pd.x
)
select y as customer_id
    ,days
    ,payment_date
    ,floor(active_value,2) as active_value
    ,floor(interest,2) as interest
    ,floor(present_value,2) as present_value
from rec_cte

giving:

CUSTOMER_ID	DAYS	PAYMENT_DATE	ACTIVE_VALUE	INTEREST	PRESENT_VALUE
1	0	2021-11-10	1,690.96	0	1,690.96
1	28	2021-12-08	1,725.49	34.53	1,420.59
1	35	2022-01-12	1,456.96	36.36	1,152.06
1	28	2022-02-09	1,175.59	23.53	870.69
1	24	2022-03-05	885.91	15.22	581.01
1	31	2022-04-05	594.16	13.15	289.26
1	30	2022-05-05	295.6	6.33	0

I was not 100% of you daily interest rate or payment, so solving those took that most time. So next please just include them.

I also would not use double money.. but my first take at use number(20,10) was not so successful..