Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Revenue Trend Analysis in SQL

Introduction
Total revenue for multiple periods
Calculating deltas
Creating "revenue in quarters" reports
Summary

Instruction

Very well done! In the previous explanation, we calculated the quarterly revenue in each year. Now, let's say we want a report containing:

  1. quarterly revenue values.
  2. annual revenue values.
  3. the grand total revenue value.

Here's an example of the result we'd like to achieve:

rollup – result example

Sound complicated? Here's a query to handle it:

SELECT
  EXTRACT(year FROM order_date) AS revenue_year, 
  EXTRACT(quarter FROM order_date) AS revenue_quarter, 
  SUM(amount) AS total_revenue 
FROM orders
GROUP BY ROLLUP(
  EXTRACT(year FROM order_date),
  EXTRACT(quarter FROM order_date)
)
ORDER BY
  EXTRACT(year FROM order_date), 
  EXTRACT(quarter FROM order_date);

We introduced a small change in the GROUP BY clause. Instead of:

GROUP BY
  EXTRACT(year FROM order_date),
  EXTRACT(quarter FROM order_date)

we used:

GROUP BY ROLLUP(
  EXTRACT(year FROM order_date),
  EXTRACT(quarter FROM order_date)
)

Let's run the query and see what happens.

Exercise

Run the template query.

As you can see, we've got rows showing three aggregation levels: the grand total revenue, annual revenues, and the quarterly revenues for each year.

In other words, now you can see:

  1. the grand total revenue – the total sum for all years (revenue_year and revenue_quarter being NULL).
  2. the annual revenues – the sums for each year (revenue_quarter being NULL).
  3. the sums for each year and quarter (being the results of the query from the pevious exercise).

Stuck? Here's a hint!