SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--Bart McEndree 2024-APR-05
--Possible solution to

CREATE TABLE Example 
(
    [Group]	VARCHAR(512),
    discount decimal(5,2),
    amount decimal(5,2)
);

INSERT INTO Example ([Group], discount, Amount) VALUES
	('1', 20, 100),
	('1', 30, 100),
	('2', 10, 100),
	('2', 10, 100),
	('3', 25, 100);

with examplegrouped as
   (
     SELECT [Group], sum(discount) as discount , min(Amount) as Amount
     FROM example e
     Group BY [Group]
   ),
    data as (
        SELECT eg.*, row_number() over( order by [Group]) rn
        FROM examplegrouped eg
    ),
    rcte as (
        select  amount, [Group], (discount/100)  as discount_pct, rn,
            cast(amount * (discount/100) as decimal(5,2))  as discount_amount,
            cast(amount * (1 - (discount/100)) as decimal(5,2)) as rest_amount
        from data
        where rn = 1
        union all
        select  r.amount, d.[Group],  (d.discount)/100 as discount_pct, d.rn,
            cast(r.rest_amount * (d.discount)/100 as decimal(5,2)) ,
            cast(r.rest_amount * (1 - (d.discount)/100)  as decimal(5,2))              
        from rcte r
        inner join data d on  d.rn = r.rn + 1
    )
select * from rcte

amount	Group	discount_pct	rn	discount_amount	rest_amount
100.00	1	0.500000	1	50.00	50.00
100.00	2	0.200000	2	10.00	40.00
100.00	3	0.250000	3	10.00	30.00