Awesome! We can also express the delta between two periods as a percentage. Look at the following query:
EXTRACT(year FROM order_date) AS revenue_year,
SUM(amount) AS total_revenue,
(SUM(amount) - LAG(SUM(amount), 1)
OVER (ORDER BY EXTRACT(year FROM order_date)))
/ CAST(SUM(amount) AS decimal(10,2)),
2) AS delta_percentage
GROUP BY EXTRACT(year FROM order_date)
ORDER BY EXTRACT(year FROM order_date);
Here is the result:
This time, the calculation in the last column is a bit more complicated:
- We divide the change between the current and previous period by the revenue value from the current period.
- To avoid integer division, we cast the denominator to a decimal type with
CAST(SUM(amount) AS decimal(10,2))). For larger numbers, you can of course increase the first argument of
- We multiply the division result by
100 to get the percentage value.
- We round the percentage to two decimal places using the
ROUND(value, 2) function.