# Forecasting when you have Seasonal effects using the Ratio to Moving Average method in SQL

Many sales activities, product adoption and other business activities – customer support, website traffic, etc. show two kinds of patterns: a upward/downward trend and a seasonal variation. For example, not only has Amazon's revenue been trending up (growing) year after year for the last 20 years, but also shows seasonality with most of the sales made in the last four months of year (specially during Black Friday and Christmas.)

As a data analyst, if you were interested in knowing whether sales is trending up or down – the seasonality will throw you off. The sales for January and February is always less than December (because of Christmas) and without correcting for seasonality, it would appear that sales is trending down.

Modeling seasonal effects is important in order to accurately predict how you need to provision resources and maintain sufficient lead time. The ratio-to-moving average method is an easy to use method to pick out the seasonal effects and forecast future values.

## 1. Determine a Seasonal Index using Moving Averages

Consider a table "amazon_revenue" with two columns: quarter and revenue. With the following query:

SELECT quarter,
revenue
FROM amazon_revenue
ORDER BY quarter ASC


the chart shows Amazon's revenue over the last 10 years for 40 quarters straight. The last quarter of every year shows a big jump year after year, alluding to the seasonal nature of retail sales.

Our first step is to deseasonalize the time series. We'll do this using a 4-quarter moving average:

SELECT quarter,
revenue,
AVG(revenue) OVER (ORDER BY quarter ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS ma
FROM amazon_revenue
ORDER BY quarter ASC


To compute the seasonal index, we simply divide the actual revenue by the moving average. A seasonal index of 1.75 means that the revenue for the quarter is 175% of the moving average, while an index of 0.5 means the revenue is half the average.

We'll take the analysis one further step and average the seasonal indices across the entire time series:

SELECT date_part('month', q) as d,
avg(revenue * 1.0 / ma) AS si
FROM
( SELECT row_number() over (ORDER BY quarter) AS n,
[quarter:quarter] AS q,
revenue,
avg(revenue) over(ORDER BY quarter ROWS BETWEEN 1 PRECEDING AND 2 following) AS ma
FROM amazon_revenue
ORDER BY q ASC) c
GROUP BY 1


which will give us a lookup table:

d si
1 0.9121396120732432
4 0.8246402054415246
7 0.8599421103521137
10 1.2633667197200424

## 2. Identifying the trend portion of the time series

The previous section showed us how to pull out the seasonality component. Next, we'll pull out the trend portion of the time series. Intuitively, Amazon's revenue is trending upwards year-over-year. We can run a regression using least squares to identify the trend portion.

We refer to our previous recipe for the mathematical background: Calculating Linear Regression Coefficients in SQL.

We'll take a shortcut and use PostgreSQL's regr_slope and regr_intercept to compute the regression line's slope and intercept. If you're not using PostgreSQL, you can compute these coefficient using the afore mentioned link on Linear Regression.

SELECT regr_slope(ma, EXTRACT(EPOCH from quarter)) as slope,
regr_intercept(ma, EXTRACT(EPOCH from quarter)) as intercept
FROM (
SELECT quarter,
revenue,
AVG(revenue) OVER (ORDER BY quarter ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS ma
FROM amazon_revenue
ORDER BY quarter ASC
) a


## 3. Forecasting using Deseasonalized data

By combining the regression as well as the seasonality index, we have now acquired the capability to forecast future sales!

To actually perform the forecast, we'll project the trend line from the linear regression into future periods, and then adjust these trend values to account for the seasonal factors.

The final SQL appears to be verbose, but we've just refactored it:

WITH base AS
( SELECT n,
q,
revenue,
ma
FROM
( SELECT row_number() over (ORDER BY quarter) AS n,
[quarter:quarter] AS q,
revenue,
avg(revenue) over(ORDER BY quarter ROWS BETWEEN 1 PRECEDING AND 2 following) AS ma
FROM amazon_revenue
ORDER BY q ASC ) b),

si AS
( SELECT date_part('month', q),
avg(revenue * 1.0 / ma) AS si
FROM
( SELECT row_number() over (ORDER BY quarter) AS n,
[quarter:quarter] AS q,
revenue,
avg(revenue) over(ORDER BY quarter ROWS BETWEEN 1 PRECEDING AND 2 following) AS ma
FROM amazon_revenue
ORDER BY q ASC) c
GROUP BY 1 ),

trend AS
( SELECT
regr_slope(ma, n) AS slope,
regr_intercept(ma, n) AS intercept
FROM
(SELECT row_number() over (ORDER BY quarter) AS n,
[quarter:quarter] AS q,
revenue,
avg(revenue) over(ORDER BY quarter ROWS BETWEEN 1 PRECEDING AND 2 following) AS ma
FROM amazon_revenue
ORDER BY q ASC) a
)

SELECT base.q,
base.revenue,
base.ma,
trend.intercept + trend.slope *  base.n * CASE date_part('month', base.q)
WHEN 1 THEN 0.9121396120732432
WHEN 4 THEN 0.8246402054415246
WHEN 7 THEN 0.8599421103521137
WHEN 10 THEN 1.2633667197200424
END as forecast
FROM trend,
base


👋 No fuss, just SQL We are open sourcing everything from the experience working with our agency clients. They spend thousands of dollars to get this level of detailed analysis – which you can now get for free. You'll learn bucketing, finding outliers, calculating correlations, budgeting & forecasting, return on spend and investment, data smoothing and cleaning applied to freemium, transactional, advertising, two-sided marketplace and subscription based business models. We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

No spam, ever! Unsubscribe any time.