By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * into Persons
from (
values
(1, Null, 'Tom '),
(2, 1 , 'Jake'),
(3, 2 , 'Kate'),
(4, 3 , 'Neil'),
(5, NULL, 'Foo'),
(6, 5, 'Bar')
)t(Id, ParentId, Name);
select * into Sales
from (
select 4 PersonId, 500.0 Price
union all select 6 PersonId, 1000.0 Price
) t;
select * from Persons;
select * from Sales;
| Id | ParentId | Name |
|---|---|---|
| 1 | null | Tom |
| 2 | 1 | Jake |
| 3 | 2 | Kate |
| 4 | 3 | Neil |
| 5 | null | Foo |
| 6 | 5 | Bar |
| PersonId | Price |
|---|---|
| 4 | 500.0 |
| 6 | 1000.0 |
WITH cte_persons
AS
(
SELECT 1 as level, p.Id, p.ParentId, p.Name, s.Price, p.Id AS base
FROM Persons AS p
INNER JOIN Sales AS s ON s.PersonId = p.Id
UNION ALL
SELECT level + 1, p.Id, p.ParentId, p.Name, c.Price, c.base
FROM Persons AS p
INNER JOIN cte_persons AS c ON c.ParentId = p.Id
)
SELECT Id, ParentId, Name,
CASE level WHEN 1
THEN price - sum(delta) over(partition by base order by level desc) + delta
ELSE delta END sale
FROM (
SELECT *,
(power (0.8000, max(level) over(partition by base) - level) * 0.2) * price delta
FROM cte_persons
) t
ORDER BY id;
| Id | ParentId | Name | sale |
|---|---|---|---|
| 1 | null | Tom | 100.000000 |
| 2 | 1 | Jake | 80.000000 |
| 3 | 2 | Kate | 64.000000 |
| 4 | 3 | Neil | 256.000000 |
| 5 | null | Foo | 200.000000 |
| 6 | 5 | Bar | 800.000000 |