CODEWITHSUNDEEP
sqlamazon-redshift
Alan
Alan

Reputation: 51

SQL statement to look at ALL the previous rows

I have the following table

create table interest_earning_assets (
  key integer
  month_of varchar(7),
  principal_distribution numeric(38,2),
  closed_loan_amount numeric(38,2)
);

Data looks like this

key month_of    principal_distribution  closed_loan_amount
24  2017-01     4133500.00              5984695.00
23  2016-12     12018303.93             26941275.40
22  2016-11     6043945.46              21239620.20
21  2016-10     2864195.39              20368518.20

I have two requirements.

  1. Sum up closed_amount_values

For each month (currently 24 months, next month 25 months, then 26 etc), I need to sum up closed_amount_values with the values of all the previous months i.e

2017-01 sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12 + 2017-01)
2016-12 sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12)
2016-11 sum(closed_loan_amount for 2016-10 + 2016-11) 
2016-10 sum(closed_loan_amount for 2016-10 )
  1. Subtract the sum of closed_loan_amount to principal_distribution

Once I have the summed up values, I need to subtract the sum of closed_loan_amount to principal_distribution for each month

2017-01 principal_distribution for 2017-01 - sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12 + 2017-01)
2016-12 principal_distribution for 2016-12 - sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12)
2016-11 principal_distribution for 2016-11 - sum(closed_loan_amount for 2016-10 + 2016-11) 
2016-10 principal_distribution for 2016-10 - sum(closed_loan_amount for 2016-10 )

Redshift does not support Stored procedures and I am not proficient in Python. So I am attempted to use lag thusly

select month_of, closed_loan_amount, 
       lag(closed_loan_amount,1) over (order by month_of desc) as previous_month 
from public.interest_earning_assets

It works but only gives me the previous month value. I am also looking into using CTEs but I just got this assignment today. How can I do that in SQL?

Upvotes: 1

Views: 141

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT [key], month_of,
       SUM(closed_loan_amount) OVER(ORDER BY month_of),
       principal_distribution + SUM(closed_loan_amount) OVER(ORDER BY month_of)
FROM interest_earning_assets

Windowed version of SUM with an ORDER BY clause calculates the running total of a field according to an order defined by a second field that appears in the ORDER BY clause.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use the sum window function with a window specification to look at all the previous rows to get the sum of closed_loan_amount and subtract it from principal_distribution.

select month_of, closed_loan_amount, 
 principal_distribution
-sum(closed_loan_amount) over (order by month_of desc rows between current row and unbounded following) as some_value
from public.interest_earning_assets

Upvotes: 1

Related Questions