Good job! Now that we know how to calculate the revenue value for the previous period, we can easily calculate the revenue change (delta) between two periods. Take a look:
DATEPART(Year, OrderDate) AS RevenueYear,
SUM(Amount) AS TotalRevenue,
SUM(Amount) - LAG(SUM(Amount), 1) OVER (ORDER BY DATEPART(Year, OrderDate)) AS Delta
GROUP BY DATEPART(Year, OrderDate)
ORDER BY DATEPART(Year, OrderDate);
Delta column, we simply subtracted the revenue generated in the previous year from the revenue generated in the current year. Once again, we used the
LAG() function pattern from the previous exercise.