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);
   

How about a quick quiz?