Time-Series Analysis with SQL

The regional analysis in the previous article compared two rolling twelve-month periods. That was enough to identify direction, but it compressed a great deal of movement into two totals.

Monthly analysis gives a more useful view. It can show whether change is sustained, whether a strong annual result depends on one unusual month, and whether the apparent pattern differs between customer groups.

It also introduces a risk that isn’t obvious from a simple chart. A month can look weak because the data is incomplete rather than because the business declined.

This article is part of a twelve-part SQL sales analytics project based on AdventureWorks2025, supported by the sql-sales-analytics-portfolio repository.

SQL Sales Analytics Project Series

1. Building a Sales Analytics Solution with SQL: Project Overview
2. Understanding the Sales Data Landscape with SQL
3. Assessing Sales Data Quality with SQL
4. Preparing Sales Data for Analysis with SQL
5. Analysing Customer Purchasing Behaviour with SQL
6. Measuring Product Performance with SQL
7. Analysing Regional Sales Performance with SQL
8. Time-Series Analysis with SQL ← You are here
9. Using Advanced SQL to Generate Business Insights
10. Optimising SQL for Analytical Workloads
11. Designing an Analytics-Friendly Data Model
12. Completing a SQL Sales Analytics Project

Reproduce the Analysis

This article depends on the analytical views created in Article 4. If the Analytics views aren’t installed, run the analytical view creation script against AdventureWorks2025 first.

Then run the complete time-series analysis script in SQL Server Management Studio. The analysis is read-only, uses a session-scoped temporary table, and includes every date-coverage, monthly, cumulative, moving-average, year-over-year, customer-mix, and month-of-year query referenced below.

The Business Problem

A monthly sales report should do more than put revenue on a line chart. I want to answer several distinct questions:

As in the previous articles, I use order SubTotal as net sales value. It excludes tax and freight, but it doesn’t represent profit.

Checking the Time Boundary First

The order view contains 31,465 orders and $109.85 million in net sales value from 30 May 2022 to 29 June 2025. That spans 38 calendar months, but the first and last dates don’t mean that all 38 months are suitable for comparison.

The observation-period query reports the overall boundary. I then check the range separately for each customer type.

The customer-type coverage query exposes a two-month difference at the end of the dataset.

May 2025 contains individual orders but no store orders. June contains individual orders only and ends on the 29th. Because store sales contribute most of the dataset’s value, treating either month as a comparable company-wide period would create a false decline.

I therefore use April 2025 as the last comparable month. May 2022 is also marked as a boundary month because the observation period begins on the 30th.

The latest date isn’t automatically the reporting cutoff

I initially found an apparent 63.47% monthly decline in May 2025. The decline disappeared as a valid company-wide comparison once I checked customer-type coverage. This is exactly why time boundaries should be validated before trend calculations are interpreted.

Building a Complete Month Series

Grouping orders by month returns only months that exist in the data. If a month has no orders, it disappears entirely and window functions treat the surrounding months as consecutive observations.

I generate every month between the first and last observed dates, then left join the monthly order totals.

WITH calendar_months AS
(
    SELECT @FirstMonth AS MonthStart

    UNION ALL

    SELECT DATEADD(month, 1, MonthStart)
    FROM calendar_months
    WHERE MonthStart < @LastMonth
)

The full monthly-series query creates one row per calendar month, fills missing results with zero, calculates average order value, and flags non-comparable boundary months.

There are no completely missing months in this sample. Generating the series is still worthwhile because the query continues to behave correctly if the data changes.

For a production reporting model, I would normally use a permanent calendar dimension rather than create the series inside each analysis. The recursive CTE keeps this article reproducible before that dimensional model exists.

Adding Running Totals and a Moving Average

Once the data has one row per month, window functions can calculate cumulative and rolling measures without changing the grain.

SUM(NetSalesValue) OVER
(
    ORDER BY MonthStart
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningNetSalesValue,

AVG(NetSalesValue) OVER
(
    ORDER BY MonthStart
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS ThreeMonthMovingAverage

The monthly window-function query returns orders, ordering accounts, net sales value, average order value, the running total, and the three-month moving average.

The running total answers how recorded value accumulates. The moving average has a different purpose: it reduces some of the month-to-month variation so the broader direction is easier to see.

Monthly net sales rise from a three-month average of $2.26 million in January 2024 to $4.59 million in April 2025. The path isn’t smooth, but the underlying level is materially higher.

The moving average shouldn’t be mistaken for a forecast. It summarises recent observations and will lag when the direction changes quickly.

Comparing Calendar Years Carefully

Only 2023 and 2024 contain twelve comparable observed months. The annual query keeps boundary-month counts visible so partial years aren’t placed beside full years without qualification.

The annual comparison query also calculates the change from the prior year.

Between the two complete calendar years, net sales increase by $12.07 million, or 38.18%. Orders increase much faster, from 3,830 to 14,244.

That divergence points back to customer mix. Individual order volume rises sharply during 2024, while lower individual average order values pull the company-wide average down. More orders doesn’t translate into sales value at the same rate.

Using Year-over-Year Comparisons

Month-over-month change is sensitive to normal calendar variation. Comparing a month with the same month one year earlier removes some of that effect, although it doesn’t control for every commercial change.

I use LAG with an offset of twelve rows after establishing a complete month sequence.

LAG(NetSalesValue, 12) OVER (ORDER BY MonthStart)
    AS PriorYearNetSalesValue

The monthly year-over-year query excludes a comparison if either month sits at a data boundary.

Every valid month from June 2023 through April 2025 records higher sales than the same month one year earlier. April 2025 has the largest recent rate, rising from $2.53 million to $5.22 million, an increase of 106.18%.

That sounds unequivocally positive, but year-over-year growth can still reflect customer mix, product launches, account wins, or unusually weak prior-year performance. It tells me the size and direction of the difference, not its cause.

Reading the Latest Comparable Month

No single benchmark is sufficient, so the final query compares April 2025 at three horizons.

April reaches $5.22 million, the highest comparable month in the dataset according to the monthly ranking query. The latest-month comparison query calculates all three benchmarks together.

The agreement across the measures gives stronger evidence of recent growth than any one comparison would provide. It still doesn’t tell me whether that level will persist.

Use several time horizons

Month-over-month change captures immediate movement, year-over-year change adds calendar context, and a moving average describes the recent level. Agreement is informative, while disagreement is a reason to investigate rather than choose the most convenient measure.

Customer Mix Changes the Shape of the Series

The monthly customer-mix query separates store and individual orders before calculating their share of monthly sales.

Individual orders increase from 324 in April 2024 to 2,098 in April 2025. Their net sales rise from $545,185 to $1.81 million, while their average order value falls from $1,683 to $861.

Store sales remain the larger financial contributor. They account for 65.42% of April 2025 net sales from only 181 orders. Individual customers generate the other 34.58% from 2,098 orders.

The overall series therefore combines two different patterns:

• Store activity: Low order volume, high average order value, and greater monthly volatility because individual orders are commercially large.
• Individual activity: High and rapidly increasing order volume, much lower average order value, and a growing share of total sales.

A company-wide line remains useful, but channel-specific lines are needed to explain what moved it.

Looking for Seasonality

Grouping comparable observations by month number gives an initial month-of-year profile.

The month-of-year query returns the average, minimum, maximum, and number of observations for every calendar month.

March, June, and September have the highest average sales. August and November are among the lowest. It would be premature to call this seasonality.

Most months have only three observations. The business also changes substantially across those years, particularly in individual order volume. A genuine seasonal assessment needs a longer, more stable history and should separate trend from recurring calendar effects.

Business Recommendations

Use April 2025 as the current company-wide cutoff

May and June should remain visible for data-quality monitoring, but they shouldn’t be used in combined trend reporting until the absence of store orders is understood.

Publish separate store and individual trends

The customer groups have different order volumes, values, and coverage boundaries. Separate reporting will make changes easier to diagnose and prevent mix shifts from being presented as uniform business movement.

Investigate the sustained 2024 and early 2025 growth

The full-year increase in 2024 and the strong comparisons through April 2025 justify deeper analysis. I would decompose growth into new customers, repeat purchasing, product families, territories, and price or discount effects.

Add explicit completeness rules to recurring reports

A production pipeline should record the expected arrival date for each source and mark a period complete only when every required feed has arrived. A maximum transaction date alone isn’t enough.

Treat month-of-year patterns as hypotheses

March, June, and September may deserve planning attention, but staffing or inventory decisions shouldn’t rely on three observations per month. Revisit the pattern when a longer history is available.

Limitations

The analysis remains constrained by the sample and analytical layer.

• Short history: Most calendar months have only three comparable observations.
• Coverage mismatch: Store and individual orders end on different dates.
• Partial years: 2022 and 2025 aren’t suitable for direct full-year comparison.
• Changing mix: Rapid growth in individual orders affects totals and average order value.
• Calendar effects: The analysis doesn’t adjust for working days, holidays, promotions, or trading calendars.
• Causality: Trend calculations describe movement but don’t explain its commercial cause.
• Forecasting: Moving averages and historical growth rates aren’t forecasts.
• Profitability: Net sales value doesn’t include cost or margin.

The time series provides a sound descriptive view once its boundaries are respected. Forecasting and formal seasonal modelling would need a longer and better-documented history.

Next in the Series

The next article will use more advanced SQL to generate business insights.

I will apply ranking, percentiles, cohort-style comparisons, Pareto analysis, and other window-function techniques to questions that need more than a straightforward aggregate.