Last active
July 24, 2024 03:40
-
-
Save pranithan-kang/b482aecd839131bb4cbb5323c751ae9a to your computer and use it in GitHub Desktop.
step-fee-calc
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
drop function get_fee_master; | |
create or replace function get_fee_master (jsonrate jsonb) | |
returns table (fm_start_range decimal, fm_end_range decimal, fm_rate decimal, fm_acc_prev_fee decimal) | |
language plpgsql | |
as $$ | |
begin | |
return query | |
with rate_master as ( | |
select | |
coalesce(lag(amount) over (order by amount), 0) as start_range, | |
amount as end_range, | |
amount - coalesce(lag(amount) over (order by amount), 0) as gap, | |
rate as rate | |
from jsonb_to_recordset(jsonrate) as r(rate decimal, amount decimal) | |
), | |
max_fee as ( | |
select *, gap * rate / (100 + rate) as max_fee | |
from rate_master | |
), | |
fee_master as ( | |
select start_range, end_range, rate, coalesce(lag(max_fee) over (order by end_range), 0) as prevmax_fee | |
from max_fee | |
) | |
select start_range, end_range, rate, sum(prevmax_fee) over (order by end_range) as acc_prev_fee | |
from fee_master; | |
end; | |
$$; | |
-- unit testing | |
select * from get_fee_master('[ | |
{"rate": "15", "amount": "500"}, | |
{"rate": "10", "amount": "1000"}, | |
{"rate": "5", "amount": "3000"}, | |
{"rate": "3", "amount": "5000"}, | |
{"rate": "1", "amount": "20000"} | |
]'::jsonb); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment