Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Total revenue for multiple periods
Calculating deltas
Creating "revenue in quarters" reports
Summary
17. Recap

## Instruction

That's all we wanted to teach you in this part. It's time to wrap things up! First, let's review what we've learned:

1. To show the total revenue for each year, use EXTRACT():
SELECT
EXTRACT(year FROM order_date) AS revenue_year,
SUM(amount) AS total_revenue
FROM orders
GROUP BY EXTRACT(year FROM order_date)
ORDER BY EXTRACT(year FROM order_date);

2. To show the total revenue for each quarter/month in each year, use EXTRACT() twice:
SELECT
EXTRACT(year FROM order_date) AS revenue_year,
EXTRACT(quarter FROM order_date) AS revenue_quarter,
...
GROUP BY
EXTRACT(year FROM order_date),
EXTRACT(quarter FROM order_date);

3. To show the total revenue on all aggregation levels, add ROLLUP():
...
GROUP BY ROLLUP(
EXTRACT(year FROM order_date),
EXTRACT(quarter FROM order_date)
)
...

4. To show the revenue from the previous year, use LAG():
LAG(SUM(amount), 1) OVER (ORDER BY EXTRACT(year FROM order_date))

5. To calculate the difference between the current period and the previous period, use:
SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY EXTRACT(year FROM order_date)) AS delta

6. To calculate the difference between the current period and the previous period as a percentage, use:
ROUND(100 * (
(SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY EXTRACT(year FROM order_date)))
/ CAST(LAG(SUM(amount), 1) OVER (ORDER BY EXTRACT(year FROM order_date)) AS float)
), 2) AS delta_percentage

7. To create a revenue in quarters report, use:
SELECT
EXTRACT(year FROM order_date) AS year,
SUM(CASE WHEN EXTRACT(quarter FROM order_date) = 1 THEN amount ELSE 0 END) AS Q1,
...
FROM orders
GROUP BY EXTRACT(year FROM order_date)
ORDER BY EXTRACT(year FROM order_date);