SQL Query Payments due after threshold met

Question

We payout commission to sales agents for fees collected from clients, but only after a threshold has been met. In my example, the threshold for this client is $3,166.09 and as we collect fees, they've met the threshold by the 8/12/2021 transaction and gone over a bit when you look at the cumulative total amount of fees collected to that point. I'm just getting hung up on how to calculate that last column of "COMMISSION_PMT"

DESIRED RESULTS:

CUST_ID	PMT_DATE	THRESHOLD	FEE_COLLECTED	RUNNING_TTL	COMMISSION_PMT
110	2021-04-28	3166.09	764.25	764.25	0
110	2021-05-11	3166.09	449.75	1214	0
110	2021-07-27	3166.09	1709	2923	0
110	2021-08-12	3166.09	910.5	3833.5	667.41
110	2021-08-31	3166.09	164.91	3998.41	164.91
110	2021-09-27	3166.09	594.75	4593.16	594.75
110	2021-10-01	3166.09	1343.25	5936.41	1343.25

select
t.cust_id
, t.pmt_date
, c.threshold
, sum(t.fee_collected) over(partition by t.cust_id order by t.pmt_date asc) as running_ttl
, case
    when sum(t.fee_collected) over(partition by t.cust_id order by t.pmt_date asc) < c.threshold
    then 0
    else t.fee_collected
 end as commission_pmt

from transactions t inner join customers c on t.cust_id = c.id order by 2 asc

Answer 1

I think, I got it:

select t.cust_id, t.pmt_date, t.threshold, t.fee_collected,
  case when t.part_row_number = t.max_part_row_number and t.temp_cash > t.threshold then t.temp_cash else 0 end commission_pmt
from (
  select t.*,
    max(part_row_number) over(partition by t.cust_id, t.part) max_part_row_number
  from (
    select t.*,
      row_number() over(partition by t.cust_id, t.part) part_row_number,
      sum(t.fee_collected) over(partition by t.cust_id, t.part) temp_cash
    from (
      select t.*, 
        floor((t.ttl - t.fee_collected) / t.threshold) part
      from (
        select t.*,
          sum(t.fee_collected) over(partition by t.cust_id order by t.pmt_date) ttl
        from payments t
      ) t
    ) t
  )t
)t;

yields:

110|2021-04-28|3166.09|  764.25|0
110|2021-05-11|3166.09|  449.75|0
110|2021-07-27|3166.09| 1709.00|0
110|2021-08-12|3166.09|  910.50|3833.5
110|2021-08-31|3166.09|  164.91|0
110|2021-09-27|3166.09|  594.75|0
110|2021-10-01|3166.09| 1343.25|0

1. ttl: the collected fees are summed up per customer over the dates.
2. part: the partition is created for each threshold area.
3. part_row_number: row number inside each partition and temp_cash, the summed up collected fee in each partition part.
4. max_part_row_number of each partition part.
5. commission_pmt is each row with max_part_row_number... if it's greater than threshold.

You can test it here: https://replit.com/@rasenderhase/SQL-Query-Payments-due-after-threshold-met?v=1

Cheers.