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

Good job! Now, let's say we want to compare revenue changes month by month. We can pick a single year and show the revenue for each month. Check it out:

SELECT
  EXTRACT(month FROM order_date) AS month_in_2016,
  SUM(amount) AS total_revenue 
FROM orders
WHERE order_date >= '2016-01-01' AND order_date < '2017-01-01'
GROUP BY EXTRACT(month FROM order_date)
ORDER BY EXTRACT(month FROM order_date);

The result looks like this:

month_in_2016 total_revenue
... ...
8 25485.28
9 26381.40
10 37515.73
... ...

To extract the months, we used EXTRACT(month FROM order_date) instead of EXTRACT(year FROM order_date). Note two things:

  1. months are shown as integers from 1 to 12.
  2. months don't contain any information about the year, so we typically pick a single year using WHERE (as we did in the above example.) If you don't use WHERE to select the year, you'd get monthly revenue values summed across all years. In other words, the January revenue would show the sum of January 2016, January 2017, and January 2018.

Exercise

We can also use EXTRACT() to get quarterly info, which is frequently used in financial analyses. The statement below ...

EXTRACT(quarter FROM date) = 1

... signifies the period from January through March. The following statement:

EXTRACT(quarter FROM date) = 2

... signifies the period from April through June. And so on for the other two quarters.

Show the total revenue from each quarter of 2017. Show two columns: quarter_in_2017 and total_revenue.

Order the rows by quarter.

Stuck? Here's a hint!

Use the word quarter as the first argument of the EXTRACT() function.