SQL Query: Comparing Monthly Sales with Different Timeframes

Analyzing sales trends is essential for understanding business performance. In this tutorial, we’ll explore three SQL strategies to compare monthly sales figures using the AdventureWorks data warehouse. Whether you're a data analyst or preparing for SQL interviews, these methods will improve your ability to analyze time-based data.

We’ll cover three scenarios for monthly comparisons:

1. Compare the current month’s sales with the previous month.
2. Compare the current month’s sales with the same month last year.
3. Compare the current month’s sales with the first month of the same year.

Let’s dive into each scenario step by step.

Comparing Current Month Sales vs Previous Month

To begin, we need to calculate monthly sales from the FactResellerSales table in the AdventureWorks data set. The data is at the day level, so we’ll aggregate it by month.

Step 1: Aggregate Data by Month

First, we summarize the sales using SUM grouped by year and month.

SELECT  
    YEAR(OrderDate) AS SalesYear,  
    MONTH(OrderDate) AS SalesMonth,  
    SUM(SalesAmount) AS MonthlySales  
FROM FactResellerSales  
GROUP BY YEAR(OrderDate), MONTH(OrderDate);  

This query provides the total sales for each month.

Step 2: Use the LAG Function for Prior Month Comparison

To compare a month’s sales with the prior month’s, we use the LAG window function. It retrieves the value from the previous row when ordered by year and month.

SELECT  
    YEAR(OrderDate) AS SalesYear,  
    MONTH(OrderDate) AS SalesMonth,  
    SUM(SalesAmount) AS MonthlySales,  
    LAG(SUM(SalesAmount)) OVER (PARTITION BY YEAR(OrderDate) ORDER BY MONTH(OrderDate)) AS PreviousMonthSales  
FROM FactResellerSales  
GROUP BY YEAR(OrderDate), MONTH(OrderDate);  

Here’s how it works:

• PARTITION BY YEAR(OrderDate) ensures the comparison stays within the same year.
• ORDER BY MONTH(OrderDate) orders the rows so LAG can fetch the previous month’s value.

Step 3: Calculate Monthly Change

Finally, subtract the previous month’s sales to find the difference.

SELECT  
    YEAR(OrderDate) AS SalesYear,  
    MONTH(OrderDate) AS SalesMonth,  
    SUM(SalesAmount) AS MonthlySales,  
    LAG(SUM(SalesAmount)) OVER (PARTITION BY YEAR(OrderDate) ORDER BY MONTH(OrderDate)) AS PreviousMonthSales,  
    SUM(SalesAmount) - LAG(SUM(SalesAmount)) OVER (PARTITION BY YEAR(OrderDate) ORDER BY MONTH(OrderDate)) AS SalesDifference  
FROM FactResellerSales  
GROUP BY YEAR(OrderDate), MONTH(OrderDate);

This gives the sales difference month to month. For January each year, the PreviousMonthSales column will be NULL since there’s no data for December of the previous year in this partition.

Comparing Current Month Sales vs Same Month Last Year

Sometimes, you need to compare a month’s performance with the same month from the previous year. For example, compare July 2023 to July 2022. Here’s how to do that.

Use the LAG Function with a 12-Month Offset

In this case, we use the same methodology as before but specify an offset of 12 rows in the LAG function.

SELECT  
    YEAR(OrderDate) AS SalesYear,  
    MONTH(OrderDate) AS SalesMonth,  
    SUM(SalesAmount) AS MonthlySales,  
    LAG(SUM(SalesAmount), 12) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS SameMonthLastYearSales,  
    SUM(SalesAmount) - LAG(SUM(SalesAmount), 12) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS SalesDifference  
FROM FactResellerSales  
GROUP BY YEAR(OrderDate), MONTH(OrderDate);

Key details:

• The LAG function’s second parameter, 12, specifies we’re fetching the data from 12 rows back, which corresponds to the same month last year.
• For the first year in the data set, comparison values will be NULL because the prior year doesn’t exist.

Comparing Current Month Sales vs First Month of the Year

To assess growth from the start of the year, you can compare any month’s sales to those from January of the same year.

Calculate First Month Sales Using Conditional Logic

First, determine the sales for January each year. Then assign that value to all rows of the same year using a calculated offset.

Here’s the query:

SELECT  
    YEAR(OrderDate) AS SalesYear,  
    MONTH(OrderDate) AS SalesMonth,  
    SUM(SalesAmount) AS MonthlySales,  
    FIRST_VALUE(SUM(SalesAmount)) OVER (PARTITION BY YEAR(OrderDate) ORDER BY MONTH(OrderDate)) AS FirstMonthSales,  
    SUM(SalesAmount) - FIRST_VALUE(SUM(SalesAmount)) OVER (PARTITION BY YEAR(OrderDate) ORDER BY MONTH(OrderDate)) AS SalesDifference  
FROM FactResellerSales  
GROUP BY YEAR(OrderDate), MONTH(OrderDate);

Here’s the breakdown:

• FIRST_VALUE fetches the sales for the first month (January) in each year.
• PARTITION BY YEAR(OrderDate) ensures calculations reset for a new year, so only January’s data is considered as the reference.

For each month, you get both the comparison value (January sales) and the difference.

Wrapping Up

These SQL strategies make it easy to track monthly sales trends under different scenarios. Here’s a quick recap:

• Compare the current month’s sales with the previous month using the LAG function.
• Compare the current month’s sales with the same month last year by adjusting the LAG offset to 12.
• Compare the current month’s sales with the first month of the same year using FIRST_VALUE.

These techniques are versatile and can help you uncover key insights in business data. Whether it's for reporting, dashboards, or interview prep, mastering these queries is a valuable skill.

Got questions? Drop a comment or reach out—good luck with your SQL practice!

Learn More

For any questions, reach out to learn@knowstar.org.

Consider expanding your expertise with these certifications:

• Google Data Analytics Professional Certificate: https://imp.i384100.net/OR37oQ
• Google Advanced Data Analytics Professional Certificate: https://imp.i384100.net/eK1WmQ

• Best SQL and Data Analytics Books

• T-SQL Fundamentals (By Itzik Ben-Gan) - https://amzn.to/4koKGdC
• Ace the Data Science Interview - https://amzn.to/3D2ne5n
•