Demand Forecasting in SQL: Predict Next Quarter's Sales

Objective

You have monthly sales in a table and someone needs to know what to order for next quarter. The usual route is an export to CSV, a notebook, pandas, and a number pasted into a spreadsheet nobody can reproduce in six weeks.

This recipe forecasts demand where the data already lives. One CREATE EXPERIMENT fits several time-series models, scores each on periods it was not trained on, and keeps the winner. Three things make the result trustworthy rather than decorative: validation is chronological, the season length is declared, and the model is measured against the naive baseline it has to beat.

Step 1: Five years of monthly sales

Sixty months with a Q4 peak, a steady upward trend, and month-to-month variation — the shape most retail and B2B demand actually has. Five full cycles matter: seasonal models need more than one complete cycle inside each validation fold, and three years is not enough (see Step 4).

-- Drop first so the recipe is re-runnable: CREATE TABLE IF NOT EXISTS leaves an
-- existing table in place and the INSERT below then fails on duplicate keys.
DROP TABLE IF EXISTS recipe_fc_sales;

CREATE TABLE recipe_fc_sales (
  month_index  INTEGER PRIMARY KEY,
  month_label  TEXT,
  units_sold   DOUBLE
);

INSERT INTO recipe_fc_sales (month_index, month_label, units_sold) VALUES
  (1,'2021-01',1180), (2,'2021-02',1090), (3,'2021-03',1285), (4,'2021-04',1285),
  (5,'2021-05',1380), (6,'2021-06',1270), (7,'2021-07',1250), (8,'2021-08',1305),
  (9,'2021-09',1385), (10,'2021-10',1695), (11,'2021-11',2040), (12,'2021-12',2340),
  (13,'2022-01',1330), (14,'2022-02',1240), (15,'2022-03',1435), (16,'2022-04',1435),
  (17,'2022-05',1530), (18,'2022-06',1420), (19,'2022-07',1400), (20,'2022-08',1455),
  (21,'2022-09',1535), (22,'2022-10',1845), (23,'2022-11',2190), (24,'2022-12',2490),
  (25,'2023-01',1480), (26,'2023-02',1390), (27,'2023-03',1585), (28,'2023-04',1585),
  (29,'2023-05',1680), (30,'2023-06',1570), (31,'2023-07',1550), (32,'2023-08',1605),
  (33,'2023-09',1685), (34,'2023-10',1995), (35,'2023-11',2340), (36,'2023-12',2640),
  (37,'2024-01',1630), (38,'2024-02',1540), (39,'2024-03',1735), (40,'2024-04',1735),
  (41,'2024-05',1830), (42,'2024-06',1720), (43,'2024-07',1700), (44,'2024-08',1755),
  (45,'2024-09',1835), (46,'2024-10',2145), (47,'2024-11',2490), (48,'2024-12',2790),
  (49,'2025-01',1780), (50,'2025-02',1690), (51,'2025-03',1885), (52,'2025-04',1885),
  (53,'2025-05',1980), (54,'2025-06',1870), (55,'2025-07',1850), (56,'2025-08',1905),
  (57,'2025-09',1985), (58,'2025-10',2295), (59,'2025-11',2640), (60,'2025-12',2940);

Step 2: Ground truth — look at the season before you model it

SELECT
  CASE WHEN month_index % 12 = 0 THEN 12 ELSE month_index % 12 END AS calendar_month,
  ROUND(AVG(units_sold), 0) AS avg_units,
  COUNT(*)                  AS years
FROM recipe_fc_sales
GROUP BY CASE WHEN month_index % 12 = 0 THEN 12 ELSE month_index % 12 END
ORDER BY avg_units DESC;

Expected: December peaks at 2,640 units and February troughs at 1,390 — a 1.9× swing that repeats in all five years. A model that misses that is not worth deploying, which is what Step 6 checks.

Step 3: Establish the baseline you have to beat

Do this before training, so the bar is set honestly. "Same month last year" is the rule a competent analyst uses with no model at all:

SELECT ROUND(AVG(ABS(a.units_sold - b.units_sold)), 1) AS naive_mae_units
FROM recipe_fc_sales a
JOIN recipe_fc_sales b ON a.month_index = b.month_index + 12;

Expected: 150.0 units of mean absolute error. Any model that cannot beat 150 is not worth the operational cost of owning it.

Step 4: Train

CREATE EXPERIMENT recipe_fc_demand AS
  SELECT month_index, units_sold FROM recipe_fc_sales
WITH (
  task_type           = 'forecasting',
  target_column       = 'units_sold',
  time_column         = 'month_index',
  algorithms          = ['seasonal_naive','ets','prophet'],
  seasonal_period     = 12,
  forecast_horizon    = 6,
  validation_strategy = 'walk_forward',
  optimization_metric = 'mae',
  max_trials          = 30,
  random_seed         = 42
);

Two options carry most of the weight.

seasonal_period = 12 tells the engine the cycle is twelve months. Leave it out and the forecast silently degrades to trend extrapolation — measured on this series, MAE goes from 87 to 742, and January comes back near the December peak. Nothing errors. It is the single most important option here.

validation_strategy = 'walk_forward' trains on a prefix and tests on what comes next. A random split lets the model see the future and then grades it on the past, which flatters the score and tells you nothing.

Which algorithms actually work, measured

Not every name in the catalogue fits every series, and the failures are quiet. On this 60-month series:

Also measured: ets ignores max_trials. It runs 3 trials whether you ask for 3, 20 or 60, so raising the budget will not rescue it.

Step 5: Read the result honestly

CREATE EXPERIMENT returns its result — there is no system table to query afterwards. The statement above answers with a JSON document:

{
  "status": "success",
  "best_score": -87.44,
  "score_basis": "walk_forward",
  "total_trials": 7,
  "trials_attempted": 7,
  "failed_trials": [],
  "optimization_metric": "mae",
  "ignored_options": []
}

Four fields are worth reading every time:

• best_score is a negated loss. -87.44 is an MAE of 87.4 units. Closer to zero is better; the minus sign is not an error. Against the 150.0 baseline from Step 3, that is a 42% reduction in error — the number that justifies owning the model.
• score_basis confirms the validation you asked for was used. If it says holdout, your strategy was not applied.
• failed_trials is not noise. Add sarima to the list above and it appears here with its reason, while the experiment still succeeds on what did fit.
• ignored_options lists anything the engine did not understand. Empty means every option was honoured.

Step 6: Deploy and forecast

DEPLOY MODEL recipe_fc_model FROM EXPERIMENT recipe_fc_demand;

PREDICT forecast_units USING recipe_fc_model AS
  SELECT 61 AS month_index
  UNION ALL SELECT 62
  UNION ALL SELECT 63
  UNION ALL SELECT 64
  UNION ALL SELECT 65
  UNION ALL SELECT 66;

Expected: six rows of (month_index, forecast_units) covering the next half-year, in the 1,790–2,180 band — above the 1,780–1,885 the same months hit last year, because the series trends upward, and well below the 2,640 December peak.

Two things to notice.

Read the second column. Both PREDICT … USING and AUTOML.PREDICT return the input and the prediction. Taking row[0] hands you back your own input, which looks exactly like a model echoing it.

Point error is larger than the average error. The 87.4 MAE is averaged over validation windows; the single month right after the December peak is the hardest point on the curve and will be further out than that. If you are ordering stock for one specific month, carry the horizon-specific error, not the headline average.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_fc_sales;

Use it from your agent

• REST/SDK: POST /v1/query/execute with the CREATE EXPERIMENT text and read the returned JSON. Training is synchronous by default; add async = true for long series.
• Scheduled: add schedule = '0 3 1 * *' to retrain on the first of each month, once the previous month's sales have landed.
• Why in-DB: the forecast is computed beside the sales table — no export, no second copy to keep in sync, no separate scheduler to own. The model is a database object your SELECT can use.

Key Concepts Learned

• Set the baseline before you train. "Same month last year" costs one join and is the bar the model must clear. Here: 150.0 against the model's 87.4.
• Chronological validation is not optional. walk_forward tests on the future; a random split leaks and lies.
• Declare the season. Without seasonal_period, a seasonal series is forecast as a trend, silently and 8× worse.
• Forecast scores are negated losses. -87.44 is an MAE of 87.4.
• Algorithm names are not promises. arima rejects seasonal_period, sarima needs roughly five cycles, and ets ignores max_trials. Read failed_trials on every run rather than assuming the list you passed is the list that ran.