step-fee-calc

step-fee-calc.plsql

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);