subtract two rows which are result of group bc clause

Question

I am having a case where I have raw data I am grouping it according to year and then I have to subtract the year to get the value but it is not working. example data is

year, profit,gross
2020   252563.02E9   352563.02E9
2022   102563.02E9    352563.02E9
2021   352563.02E9    352563.02E9
2022   482563.02E9    352563.02E9
2021   002563.02E9     352563.02E9
2020   10231.02E9     352563.02E9
2022   345633.25E9    352563.00E9
with
cal as
(
SELECT t.year, 
cast(sum(t.profit) as decimal(17,2))  profit ,
cast(sum(t.gross)as decimal(17,2)) gross
--  t.srad_agency_provision - LAG(t.srad_agency_provision) OVER(ORDER BY year)  as diff_previous_year
from shoptable t
group by t.year
order by t.year  
)

after this query I got following result

year  profit   gross
2020  22311834.85   1030294447.42
2021  26598154.02   1704662197.69
2022  25234347.02    1841593955.36

Now I want to subtract the profit and gross from previos one.like 2021 from 2022, 2020 from 2021 I am trying

diff as (
select
  t.year,
  t.profit,
  (t.profit) - LAG(t.profit) OVER(ORDER BY year)  as diff_profit
  from
  shop table t 
  )
 

but it is showing for whole each value in 2020 and so on. I want to subtract the results of group by clause which I got in previous query. Expected output is

year  profit   gross                diff_profit    diff gross  
2020  22311834.85   1030294447.42    4,286,319.17    674,367,750.27
2021  26598154.02   1704662197.69   -1363807         136,931,757.67
2022  25234347.02    1841593955.36    

How can I achieve it?thank you

Answer 1

Should not you just apply you window function to the results of cal. Something along this lines:

-- sample data
with dataset(year, profit,gross) as (
    values
    ('2020', 252563, 352563),
    ('2022', 102563, 352563),
    ('2021', 352563, 352563),
    ('2022', 482563, 352563),
    ('2021', 002563, 352563),
    ('2020', 10231 , 352563),
    ('2022', 345633, 352563)
),
cal as (
    SELECT t.year,
    sum(t.profit) profit ,
    sum(t.gross) gross
    from dataset t
    group by t.year
    order by t.year
)

-- query
select *,
    (profit) - LAG(profit) OVER(ORDER BY year) as diff_profit,
    (gross) - LAG(gross) OVER(ORDER BY year) as diff_gross
from cal;