Gap analysis (Finance) in SQL

user_10979078 0
Viewed 72

Currently I have a quarterly table in SQL that looks like this:

Month      |   Description     |   Amount   
2021-04-01 |  transactions     |  100000
2021-07-01 |  transactions     |  166000
2021-10-01 |  transactions     |  182000

I was assigned to perform a gap analysis (Finance stuff related) that could 'smooth' the numbers into monthly expected amounts. What I would like to obtain is something similar to the following table:

Month      |Description   | smoothed amount | Original quarterly Amt
2021-04-01 |transactions  | 33333           | 100000
2021-05-01 |transactions  | 33333           |
2021-06-01 |transactions  | 99333           |
2021-07-01 |transactions  | 55333           | 166000
2021-08-01 |transactions  | 55333           |
2021-09-01 |transactions  | 71933           |
2021-10-01 |transactions  | 60867           | 182600

In which the smoothed amounts for the first 2 months are the quarterly amnt/3 (Ex. for 2021-04-01 the quarterly amt is 100000 so for that month and 2021-05-01 the smoothed amounts would be 33333 but for 2021-06-01 it would be 166000 - (33333 + 33333), the last 2 numbers on the subtraction would be the cumsum of the previous 2 smoothed amounts)

I was wondering if someone could give me a way to do this in SQL, since I don't have a clue on how to tackle this issue

snowflake-cloud-data-platform sql
1 Answers

Here is one way. Note that I am using using numeric constants like 0.3333 and 0.6666 but you might want to modify that based on your desired level of precision.

with cte (months, description, amount, original_amount) as

(select months, description, amount*0.3333, amount
from t
union all
select add_months(months,1), description, amount*0.3333, null
from t
union all
select add_months(months,2), description, lead(amount) over (partition by description order by months asc )-amount*0.6666, null
from t)

select * 
from cte 
where months<=(select max(months) from t)  ---might be optional depending on your need
order by months;
user_99471590
Related