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

Instruction

The next type of report we'd like to discuss calculates the revenue change between two periods. Such reports can help us assess whether revenue increases or decreases over time, and to what extent. We first need to learn how to show the revenue from the previous period for each row. Take a look:

SELECT
  DATEPART(Year, OrderDate) AS RevenueYear, 
  SUM(Amount) AS TotalRevenue,
  LAG(SUM(Amount), 1) OVER(ORDER BY DATEPART(Year, OrderDate)) AS PreviousYearRevenue
FROM Orders
GROUP BY DATEPART(Year, OrderDate)
ORDER BY DATEPART(Year, OrderDate);

Here is the result:

LAG() – explanation

As you can see, the report shows the total revenue for 1) each year and 2) the previous year. To do this, we used the LAG() function with an OVER() clause. LAG() shows values from row(s) that precede the current row. In this context, OVER() defines which rows are considered the "preceding rows". The statement:

LAG(SUM(Amount), 1) OVER (ORDER BY DATEPART(Year, OrderDate))

means: order all rows by the year (OVER (ORDER BY DATEPART(Year, OrderDate))), sum order values for each year (SUM(Amount)) and take the value from the previous year (LAG(SUM(Amount), 1)). The expression may look difficult, but it helps to remember it as a fixed pattern.

Note: LAG() and OVER() are used in window functions. To get a deeper understanding of how these work, see our Window Functions in MS SQL Server course.

Exercise

For each month of 2016, show the average order amount and the average order amount from the previous month. Show three columns: CalculationMonth, AvgOrderAmount, and PreviousMonthAvgOrderAmount.

In the first row, leave the previous month value as NULL. Order the rows by month.

Stuck? Here's a hint!

  1. Use a WHERE clause to select the orders from 2016.
  2. Use AVG(Amount) instead of SUM(Amount).
  3. Specify Month in the DATEPART() function.