End of Summer - 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);
    

How about a quick quiz?

Exercise

Click Next exercise to continue.