New column that subtacts 2 rows from a different column and pushing out the difference number

Question

I am doing this query, I want to be able to take 2 values from different rows from 1 column(Population growth rate) subtract them from each other, and put that value into the new column(PopGrowthRateDiff), making it repeat all the way down, making a null for the 1st value in this new column(since it has no previous number) for each new title/word in the 1st row(Country name). I likely overcomplicated this, or completely did it wrong, any help is greatly appreciated!

• this is the result I want within an SQL database:
  https://www.db-fiddle.com/f/4o1Z96ADgMEDXT1uz1Sn2D/1

• starting from:
  https://www.db-fiddle.com/f/jsFB3ELxiX8xp6Xghk21Fe/1

  select 
    FirstSet.[Country name], 
    FirstSet.Year, 
    FirstSet.[Population growth rate], 
    FirstSet.[Life expectancy at birth],
    Sum(SecondSet.PopGrowthRateDifference2 - 
    FirstSet.PopGrowthRateDifference1) as PopGrowthRateDiff
  from
  (
  Select 
    gro.[Country name], gro.Year, gro.[Population growth rate], expe. 
    [Life 
    expectancy at birth], 
    SUM( gro.[Population growth rate]) OVER 
    (partition by gro.[Country name] order by gro.[Country name], 
    gro.Year) as PopGrowthRateDifference1
  From PortfolioProject..PopulationGrowthRate$ gro
  Join PortfolioProject..LifeExpectancy$ expe
    on gro.[Country name] = expe.[Country name]
    and gro.Year = expe.Year
  ) as FirstSet
  inner join
  (
  Select 
    gro.[Country name], 
    gro.Year, 
    gro.[Population growth rate], 
    expe.[Life expectancy at birth], 
    SUM(gro.[Population growth rate]) OVER 
    (partition by gro.[Country name] order by gro.[Country name], 
    gro.Year) as PopGrowthRateDifference2
  From PortfolioProject..PopulationGrowthRate$ gro
  Join PortfolioProject..LifeExpectancy$ expe
    on gro.[Country name] = expe.[Country name]
    and gro.Year = expe.Year
  ) as SecondSet
  on FirstSet.[Country name] = SecondSet.[Country name] and 
    FirstSet.Year = SecondSet.Year and 
    FirstSet.[Population growth rate] = SecondSet.[Population growth 
    rate]
  group by 
    FirstSet.[Country name], 
    FirstSet.Year, 
    FirstSet.[Population growth rate], 
    FirstSet.[Life expectancy at birth]
  order by FirstSet.[Country name], FirstSet.Year 
  

This is my code as of now, it takes 2 queries and treats them as tables, both queries are rolling counters that reset at each new 'Country name'. Afterward, it subtracts them, which gives 0, the db-fiddle shows what I actually want to happen.

Answer 1

I believe what you actually want to happen is with the LAG() function. Link to MSDN documentation here - https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16

Here is the query to get the output desired from an already populated table, #test -

CREATE TABLE #test
(
    country_name NVARCHAR(50)
  , year INT
  , population_growth_rate FLOAT
);

INSERT INTO #test
(
    country_name
  , year
  , population_growth_rate
)
VALUES
('Argentina', 1950, 1.98)
, ('Argentina', 1951, 1.94)
, ('Argentina', 1952, 1.93)
, ('United States', 1950, 2.00)
, ('United States', 1951, 2.10)
, ('United States', 1952, 2.05);


SELECT country_name
     , year
     , population_growth_rate
     , population_growth_rate - LAG(population_growth_rate) OVER (PARTITION BY country_name ORDER BY year) AS population_growth_difference
FROM #test

Outputs this result set -

country_name                                       year        population_growth_rate population_growth_difference
-------------------------------------------------- ----------- ---------------------- ----------------------------
Argentina                                          1950        1.98                   NULL
Argentina                                          1951        1.94                   -0.04
Argentina                                          1952        1.93                   -0.01
United States                                      1950        2                      NULL
United States                                      1951        2.1                    0.1
United States                                      1952        2.05                   -0.0500000000000003

Alternatively, you could have your desired second schema and populate it this way -

CREATE TABLE #test2
(
    country_name NVARCHAR(50)
  , year INT
  , population_growth_rate FLOAT
  , population_growth_difference FLOAT
);

INSERT INTO #test2
(
    country_name
  , year
  , population_growth_rate
  , population_growth_difference
)
SELECT base.country_name
     , base.year
     , base.population_growth_rate
     , base.population_growth_rate
       - LAG(base.population_growth_rate) OVER (PARTITION BY base.country_name ORDER BY base.year) AS population_growth_difference
FROM
(
    VALUES
        ('Argentina', 1950, 1.98)
      , ('Argentina', 1951, 1.94)
      , ('Argentina', 1952, 1.93)
      , ('United States', 1950, 2.00)
      , ('United States', 1951, 2.10)
      , ('United States', 1952, 2.05)
) base (country_name, year, population_growth_rate);

SELECT country_name
     , year
     , population_growth_rate
     , population_growth_difference
FROM #test2;

Either way they create the same result set.