How to calculate Holt-Winters in SQL

Why Holt-Winters matters

It is 4pm on Friday and your product manager pings you in Slack: "Revenue spikes every weekend but the underlying trend is still climbing — what number should I put in the Monday review deck?" A plain moving average flattens the weekend bumps and looks dishonest, and exponential smoothing alone smears the weekly pattern into noise. Holt-Winters is the cheapest defensible baseline that gives you level, trend, and seasonality in one pass over the data, and it runs in pure SQL on the warehouse table you already have.

This question shows up in analytics loops at Stripe, DoorDash, Uber, Airbnb, Netflix, Snowflake, and Databricks. The phrasing is almost always the same: here is a daily metric with a weekly cycle and a slow upward drift, give me a fourteen-day forecast in SQL, then explain when the method breaks. Strong candidates name the three state equations, pick additive or multiplicative with one sentence of justification, and write the recursive CTE in under ten minutes.

Postgres, Snowflake, and Redshift all support WITH RECURSIVE, which is what Holt-Winters needs because each new state depends on the previous level, trend, and a seasonal value from one period ago. This post walks through the three recurrences, contrasts additive against multiplicative, builds the recursive query, generates an h-step forecast, and lists the traps that get candidates dinged.

The three components

Holt-Winters extends Holt's linear method by adding a third state variable for the seasonal index. At each timestep t you carry three numbers forward: L_t is the deseasonalized level, T_t is the per-period trend, and S_t is the seasonal component for the current slot in the cycle of length m. For a daily metric with a weekly pattern, m = 7; for monthly revenue with a yearly cycle, m = 12; for hourly traffic with a daily cycle, m = 24.

Each state has its own smoothing constant. Alpha controls how much the level reacts to the latest deseasonalized observation. Beta controls how quickly the trend adapts when the level changes. Gamma controls how aggressively the seasonal index updates. All three live in (0, 1). Reasonable starting values for a daily series are alpha = 0.3, beta = 0.1, and gamma = 0.2, then tune by minimizing MAE on a held-out tail.

Additive vs multiplicative

Pick additive when the seasonal amplitude stays roughly constant as the level grows. Daily active users on a mature consumer app with a stable weekly rhythm is the textbook example: weekday peaks and Sunday troughs are about the same absolute size whether the level is 800k or 1.2M. The model reads Y_t = L_t + T_t + S_{t-m} + noise.

Pick multiplicative when the seasonal swing grows in proportion to the level. Subscription revenue on a fast-growing B2B product is the canonical case: a 20 percent end-of-quarter bump on a $4M run rate is a much bigger absolute spike than the same 20 percent bump was a year ago at $1M. The model reads Y_t = (L_t + T_t) * S_{t-m} + noise. In SQL the math stays the same, you swap subtraction for division.

Rule of thumb: plot the series and look at the seasonal envelope. If the wave gets visibly fatter, use multiplicative. If it stays the same width, additive is simpler and avoids the divide-by-zero pitfall on series that can hit zero.

The additive recurrences in compact form, useful to keep next to the SQL:

L_t = alpha * (Y_t - S_{t-m}) + (1 - alpha) * (L_{t-1} + T_{t-1})
T_t = beta  * (L_t - L_{t-1})  + (1 - beta)  * T_{t-1}
S_t = gamma * (Y_t - L_t)      + (1 - gamma) * S_{t-m}
Y_hat_{t+h} = L_t + h * T_t + S_{t-m+((h-1) mod m) + 1}

Holt-Winters in SQL

Three coupled recurrences make the SQL bulkier than a single-state exponential smoothing query, but the shape is the same: bootstrap the first m periods, then let WITH RECURSIVE extend the chain one step at a time. The seasonal lookup is the awkward part because S_{t-m} is not the previous row, it is the row from one full cycle ago. The query below shows the additive form with m = 7 on a daily_metrics table that has one row per (date, metric_name, value). Treat this as the working skeleton you would extend in a real codebase, not a production-grade implementation.

WITH RECURSIVE base AS (
    SELECT
        DATE,
        value,
        ROW_NUMBER() OVER (ORDER BY DATE) AS rn
    FROM daily_metrics
    WHERE metric_name = 'dau'
),
seed_level AS (
    SELECT AVG(value)::NUMERIC AS l0
    FROM base
    WHERE rn <= 7
),
init_season AS (
    SELECT
        rn,
        (value - (SELECT l0 FROM seed_level))::NUMERIC AS s_init
    FROM base
    WHERE rn <= 7
),
hw AS (
    SELECT
        b.DATE,
        b.value,
        b.rn,
        (SELECT l0 FROM seed_level) AS l,
        0::NUMERIC                  AS t,
        s.s_init                    AS s
    FROM base b
    JOIN init_season s ON b.rn = s.rn
    WHERE b.rn = 8

    UNION ALL

    SELECT
        b.DATE,
        b.value,
        b.rn,
        0.3 * (b.value - prev.s) + 0.7 * (prev.l + prev.t)                                          AS l,
        0.1 * ((0.3 * (b.value - prev.s) + 0.7 * (prev.l + prev.t)) - prev.l) + 0.9 * prev.t        AS t,
        0.2 * (b.value - (0.3 * (b.value - prev.s) + 0.7 * (prev.l + prev.t))) + 0.8 * prev.s       AS s
    FROM base b
    JOIN hw prev ON b.rn = prev.rn + 1
)
SELECT DATE, value, l + t AS fitted, l, t, s
FROM hw
ORDER BY DATE;

Constants alpha = 0.3, beta = 0.1, gamma = 0.2 are the standard starting trio. Tuning happens outside SQL: sweep a grid in Python and pick the combination that minimizes MAE on the last four weeks. In this compact skeleton the seasonal update uses the previous row's s instead of the value from m rows back; in a fuller implementation add a LAG(s, 7) lookup over the materialized recursive output.

Forecasting with seasonality

Once the recursion reaches the final observed row, the forecast is purely arithmetic: take the last level, add h times the last trend, then add the seasonal value from the slot that matches the forecast horizon. The trick is the modular index — for h = 1 you want the seasonal value from one cycle ago in the same weekday slot, and for h = 9 on a weekly cycle you want slot ((9 - 1) mod 7) + 1 = 2.

WITH last_state AS (
    SELECT l, t, rn
    FROM hw
    ORDER BY rn DESC
    LIMIT 1
),
last_season AS (
    SELECT rn, s
    FROM hw
    WHERE rn > (SELECT rn FROM last_state) - 7
),
horizon AS (
    SELECT generate_series(1, 14) AS h
),
forecast AS (
    SELECT
        CURRENT_DATE + h.h                              AS forecast_date,
        ls.l + h.h * ls.t
            + (SELECT s FROM last_season
               WHERE rn = (SELECT rn FROM last_state) - 7
                          + ((h.h - 1) % 7) + 1)        AS y_hat
    FROM horizon h
    CROSS JOIN last_state ls
)
SELECT forecast_date, ROUND(y_hat, 0) AS forecast_value
FROM forecast
ORDER BY forecast_date;

In the interview, narrate the modular arithmetic out loud — that is the part where most candidates fumble. Read it slowly: "for horizon h, the matching seasonal slot is ((h - 1) mod m) + 1 from the start of the last full cycle." If recursive CTE performance is slow, materialize the recursive result and run the forecast off that.

Common pitfalls

The first pitfall is guessing the season length instead of measuring it. A daily metric is not always weekly — a B2B SaaS dashboard often has a five-day workweek cycle plus a monthly billing spike, and forcing m = 7 on top of that gives you a model that fights itself. Run an autocorrelation plot on the differenced series, pick the dominant lag, and only then set m.

The second pitfall is using the multiplicative form on a series that can hit zero. Multiplicative Holt-Winters divides by the seasonal index, and any zero in the seasonal seed will explode the recursion on the first step that touches it. If your DAU series has an outage day with value = 0, switch to additive or impute the zeros with a small floor before fitting.

The third pitfall is a sloppy seasonal initialization. The skeleton above uses value - average over the first cycle as the seasonal seed, which is fine for a stable series but pulls the trend in the wrong direction on a strongly growing one. The textbook fix is classical seasonal decomposition over the first two or three full cycles: detrend with a centered moving average of length m, average the residuals by seasonal slot, and use those averages as S_1 through S_m.

The fourth pitfall is leaving the smoothing constants at the textbook defaults. Alpha, beta, and gamma at (0.3, 0.1, 0.2) are reasonable starting points but not optimal for your series. Tune them by holding out the last four to eight weeks, sweeping a grid in steps of 0.1, and picking the combination with the lowest MAE on the holdout. Score the grid in pandas and write only the chosen parameters back into the production query.

The fifth pitfall is asking the model for a horizon longer than two full seasons. Holt-Winters is a local method: the seasonal index it learned is fixed once the recursion ends, so a fourteen-day forecast on a weekly cycle is fine, a four-week forecast is shaky, and anything beyond eight weeks is wishful thinking. For long horizons, switch to SARIMA or Prophet.

Optimization tips

Recursive CTEs in Postgres and Snowflake are inherently serial — each new row depends on the previous one, so the database cannot parallelize the recursion. On a multi-year hourly series at m = 24 you may have 80,000-plus steps and the query will drag. The cleanest workaround is to filter the base CTE to the last 90 to 180 days before recursing, since Holt-Winters only needs enough history to stabilize the level and one or two cycles of seasonal seeding.

If you run Holt-Winters daily on a dashboard, materialize the fitted output into a table with (date, level, trend, season) columns and only recompute the tail. Each day you append one new row using yesterday's state and today's observation, which is constant time instead of re-running the full recursion. For multi-series forecasting — one fit per country or per product — do not loop the recursive CTE per series. Run the fit in Python with statsmodels on a partitioned dataset and write the fitted parameters and the latest state back into the warehouse.

Related reading

• How to calculate exponential smoothing in SQL
• How to detrend a time series in SQL
• How to calculate autocorrelation in SQL
• How to calculate forecast bias in SQL
• SQL window functions interview questions

If you want to drill time-series SQL like this, NAILDD is launching with hundreds of SQL problems across exactly this pattern.

FAQ

Additive or multiplicative — how do I decide?

Plot the series and look at the seasonal envelope. If the amplitude of the weekly or monthly swing stays roughly constant as the level grows, use additive — more numerically stable and avoids divide-by-zero issues. If the swing grows in proportion to the level, which is typical for revenue or GMV on a fast-growing product, use multiplicative. When in doubt, fit both, score on a held-out four-week window with MAPE, and pick the lower.

How do I choose the season length m?

For a daily metric with a workweek pattern, m = 7. For monthly revenue with an annual cycle, m = 12. For hourly traffic with a daily rhythm, m = 24. Do not guess — run an autocorrelation function on the differenced series and look for the dominant lag, or compute a periodogram and pick the peak frequency. If two seasonalities are roughly equal, pick the dominant one and accept residual error on the other.

Holt-Winters versus Prophet — which should I use?

Prophet handles multiple seasonalities, holiday effects, and changepoints out of the box, and it returns confidence intervals without extra work. Holt-Winters has fewer parameters, runs in SQL with no Python dependency, and is easier to explain in an interview. For a standup baseline that stays close to the warehouse, prefer Holt-Winters. For a customer-facing forecast with holiday effects, prefer Prophet.

Why does my SQL implementation run so slowly?

Recursive CTEs are serial by construction — the database walks the chain one step at a time. On series longer than 10,000 rows you will feel it. Three fixes: filter the input to the last 90 to 180 days before recursing, materialize the fitted state and append daily instead of recomputing, or move the fit to Python and use SQL only to serve the forecast.

How should I evaluate forecast accuracy?

Split your series into train and test: keep the last four to eight weeks as the test set and fit on everything before. Compute MAE and MAPE on the test set and compare against a naive baseline like "predict last week's value" or "predict the seven-day moving average." If Holt-Winters does not beat both baselines, fall back to exponential smoothing.

Should I retune alpha, beta, and gamma every day?

No. Retune on a weekly or monthly cadence — daily retuning chases noise and makes the forecast jittery. Hold the parameters fixed, monitor MAPE on a rolling test set, and only retune when the rolling error drifts upward by more than a configured threshold.