Skip to content

Method Selection — Test Sheet

SQL examples predate the v6 unique_id migration

The SQL snippets on this page use the legacy unique_id column, which was dropped from every table in the v6 migration. All series are now keyed by item_id + site_id — replace unique_id selection / filtering / joining / ORDER BY / GROUP BY with item_id, site_id (and the built-in item_name / site_name columns where applicable). This page is retained as a historical test artifact; do not copy its SQL verbatim.

Date: 2026-05-04 Pipeline tested: theBicycle, pipeline_id=4, scenario_id=4 Scope: Best-method scoring logic, composite weights, routing by series type

TOC

  1. Scoring formula
  2. Test 1 — Overall distribution is sane
  3. Test 2 — Seasonal series prefer MSTL / AutoARIMA
  4. Test 3 — Non-seasonal series prefer AutoETS / AutoARIMA
  5. Test 4 — Head-to-head score verification (1_342)
  6. Test 5 — Head-to-head score verification (2_305)
  7. Test 6 — Runner-up method is always present
  8. Test 7 — Intermittent series routing
  9. Test 8 — No orphan series (every scored series has a forecast)
  10. Test 9 — Duplicate backtest rows do not skew scoring
  11. Known issues / flags
  12. How to drive the test

Scoring formula

Composite score = weighted average of normalised per-metric rank across all competing methods. Weights (Default parameter set, scenario.config_parameters type=best_method):

Metric Weight Direction
MAE 0.40 lower = better
RMSE 0.20 lower = better
Bias 0.15 closer to 0 = better
Coverage 90 0.15 higher = better
MASE 0.10 lower = better

The method with the lowest composite score wins. The score is computed over the __AGG__ aggregated backtest row (all rolling windows averaged).

Verify the current weights:

-- PostgreSQL (thebicycle)
SELECT name,
       parameters_set->'weights'            AS weights,
       parameters_set->'method_overrides'   AS overrides
FROM scenario.config_parameters
WHERE parameter_type = 'best_method' AND is_default = TRUE;

Expected: 

{
  "mae": 0.4,
  "rmse": 0.2,
  "bias": 0.15,
  "coverage_90": 0.15,
  "mase": 0.1
}

Test 1 — Overall distribution is sane

Objective: The four statistical methods are represented; AutoETS is limited to non-seasonal series only (after the 2026-05-03 seasonal group fix).

-- ClickHouse
SELECT best_method,
       count()                       AS series_count,
       round(avg(best_score), 4)     AS avg_composite_score
FROM PIPE_series_best_methods
WHERE pipeline_id = 4
GROUP BY best_method
ORDER BY series_count DESC;

Observed 2026-05-04:

best_method series_count avg_score
AutoARIMA 217 0.0988
AutoTheta 163 0.0822
MSTL 155 0.1346
AutoETS 29 0.1494

Pass criteria: - AutoETS series count < 100 (was 185 before the seasonal group fix) - AutoARIMA + AutoTheta together > 350 series - All four methods present

Test 2 — Seasonal series prefer MSTL / AutoARIMA

Objective: Series flagged has_seasonality=1 must not have AutoETS as best method (AutoETS is no longer in the seasonal method group).

-- ClickHouse
SELECT sc.has_seasonality,
       bm.best_method,
       count()                       AS series_count
FROM PIPE_series_best_methods bm
JOIN PIPE_series_characteristics sc
    ON  bm.unique_id   = sc.unique_id
    AND bm.pipeline_id = sc.pipeline_id
    AND bm.scenario_id = sc.scenario_id
WHERE bm.pipeline_id = 4
  AND bm.scenario_id = 4
GROUP BY sc.has_seasonality, bm.best_method
ORDER BY sc.has_seasonality DESC, series_count DESC;

Observed 2026-05-04:

has_seasonality best_method series_count
1 AutoARIMA 187
1 AutoTheta 108
1 MSTL 86
0 MSTL 69
0 AutoTheta 55
0 AutoARIMA 30
0 AutoETS 29

Pass criteria: - has_seasonality=1 rows: AutoETS count = 0 - has_seasonality=0 rows: AutoETS is permitted (it is in the standard group) - Seasonal series dominated by AutoARIMA + AutoTheta + MSTL

Test 3 — Non-seasonal series prefer AutoETS / AutoARIMA

Objective: The 29 AutoETS winners are all non-seasonal series.

-- ClickHouse: confirm AutoETS winners are non-seasonal
SELECT bm.best_method,
       sc.has_seasonality,
       sc.complexity_level,
       count() AS cnt
FROM PIPE_series_best_methods bm
JOIN PIPE_series_characteristics sc
    ON  bm.unique_id = sc.unique_id
    AND bm.pipeline_id = sc.pipeline_id
    AND bm.scenario_id = sc.scenario_id
WHERE bm.pipeline_id = 4
  AND bm.scenario_id = 4
  AND bm.best_method = 'AutoETS'
GROUP BY bm.best_method, sc.has_seasonality, sc.complexity_level
ORDER BY cnt DESC;

Pass criteria: - Every row: has_seasonality = 0 - No AutoETS winner with has_seasonality = 1

Spot-check five AutoETS winners:

-- ClickHouse
SELECT bm.unique_id, bm.best_method, bm.runner_up_method,
       sc.has_seasonality, round(sc.seasonal_strength, 3) AS ss,
       sc.complexity_level
FROM PIPE_series_best_methods bm
JOIN PIPE_series_characteristics sc
    ON  bm.unique_id = sc.unique_id
    AND bm.pipeline_id = sc.pipeline_id
    AND bm.scenario_id = sc.scenario_id
WHERE bm.pipeline_id = 4
  AND bm.scenario_id = 4
  AND bm.best_method = 'AutoETS'
ORDER BY sc.n_observations DESC
LIMIT 5;

Sample observed:

unique_id best_method runner_up has_seasonality ss complexity
22_344 AutoETS (runner_up) 0 0.000 low
9_333 AutoETS (runner_up) 0 0.000 low
13_342 AutoETS (runner_up) 0 0.000 low

Test 4 — Head-to-head score verification (1_342)

Objective: Manually verify that the declared best method (MSTL) has a lower or equal composite score than its competitors, given the observed backtest metrics.

Series: 1_342 — seasonal (ss=0.671), non-intermittent

-- ClickHouse: raw backtest metrics for 1_342
SELECT method,
       round(avg_mase,  4) AS mase,
       round(avg_smape, 4) AS smape,
       round(avg_bias,  4) AS bias,
       round(avg_coverage_90, 3) AS cov90,
       n_windows
FROM PIPE_series_backtest_metrics
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND unique_id   = '1_342'
  AND forecast_origin = '__AGG__'
GROUP BY method, avg_mase, avg_smape, avg_bias, avg_coverage_90, n_windows
ORDER BY method;

Observed metrics:

method mase smape bias cov90 n_windows
AutoARIMA 0.8807 47.73 -4.42 0.562 4
AutoTheta 0.9260 58.78 -4.59 0.625 4
MSTL 0.848 47.77 -4.27 0.625 4

MSTL wins on MASE (0.848 < 0.881) and bias (closest to 0) and ties on cov90 (0.625). With weights mae=0.4, rmse=0.2, mase=0.1, bias=0.15, cov90=0.15 — MSTL should score lowest.

-- ClickHouse: confirm declared winner
SELECT unique_id, best_method, runner_up_method, round(best_score, 4) AS score
FROM PIPE_series_best_methods
WHERE pipeline_id = 4
  AND unique_id   = '1_342';

Expected: best_method = 'MSTL', runner_up_method = 'AutoARIMA'

Test 5 — Head-to-head score verification (2_305)

Objective: For a series where AutoARIMA wins over MSTL, verify the metrics justify it.

Series: 2_305 — seasonal (ss=0.597), AutoARIMA wins

-- ClickHouse
SELECT method,
       round(avg_mase,  4) AS mase,
       round(avg_smape, 4) AS smape,
       round(avg_bias,  4) AS bias,
       round(avg_coverage_90, 3) AS cov90,
       n_windows
FROM PIPE_series_backtest_metrics
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND unique_id   = '2_305'
  AND forecast_origin = '__AGG__'
GROUP BY method, avg_mase, avg_smape, avg_bias, avg_coverage_90, n_windows
ORDER BY method;

Observed metrics:

method mase smape bias cov90 n_windows
AutoARIMA 0.891 30.62 -0.228 0.906 4
AutoTheta 1.083 34.95 -0.962 0.906 4
MSTL 1.022 35.31 -0.419 0.906 4

AutoARIMA wins on every metric. Correct.

-- ClickHouse: confirm declared winner
SELECT unique_id, best_method, runner_up_method, round(best_score, 4)
FROM PIPE_series_best_methods
WHERE pipeline_id = 4
  AND unique_id   = '2_305';

Expected: best_method = 'AutoARIMA', runner_up_method = 'MSTL'

Test 6 — Runner-up method is always present

Objective: Every scored series must have a non-empty runner_up_method (implies at least 2 methods competed).

-- ClickHouse: series with missing runner-up
SELECT count() AS missing_runnerup
FROM PIPE_series_best_methods
WHERE pipeline_id = 4
  AND (runner_up_method = '' OR runner_up_method IS NULL);

Pass criteria: result = 0

-- ClickHouse: distribution of how many methods competed per series
SELECT methods_competed, count() AS series_count
FROM (
    SELECT unique_id, count(DISTINCT method) AS methods_competed
    FROM PIPE_series_backtest_metrics
    WHERE pipeline_id = 4 AND scenario_id = 4 AND forecast_origin = '__AGG__'
    GROUP BY unique_id
)
GROUP BY methods_competed
ORDER BY methods_competed;

Pass criteria: No series with methods_competed = 1 (single-method series cannot produce a meaningful runner-up)

Test 7 — Intermittent series routing

Objective: Series with is_intermittent=1 should be routed to the intermittent method group (CrostonOptimized / ADIDA / IMAPA / MSTL). The method_overrides.intermittent config key sets a preferred winner.

-- ClickHouse: best method for intermittent series
SELECT bm.best_method,
       count() AS cnt,
       round(avg(sc.adi), 1) AS avg_adi,
       round(avg(sc.zero_ratio), 2) AS avg_zero_ratio
FROM PIPE_series_best_methods bm
JOIN PIPE_series_characteristics sc
    ON  bm.unique_id = sc.unique_id
    AND bm.pipeline_id = sc.pipeline_id
    AND bm.scenario_id = sc.scenario_id
WHERE bm.pipeline_id = 4
  AND bm.scenario_id = 4
  AND sc.is_intermittent = 1
GROUP BY bm.best_method
ORDER BY cnt DESC;

Observed 2026-05-04 (item 4 — Cassette SRAM Red AXS, all sites, adi ~210, zero_ratio=1.0):

best_method cnt avg_adi avg_zero_ratio
MSTL ~13 ~211 1.0

Flag: The method_overrides.intermittent = 'AutoTheta' config key suggests AutoTheta should be the override winner for intermittent series, but MSTL is winning on backtest score. Two possible explanations: 1. The method_overrides key is a fallback (used when no backtest data exists), not a forced winner — the scoring still runs and picks the empirical best. 2. MSTL genuinely scores better because all demand is zero (any method with zero forecast ties on MAE=0; tie-breaking may favour MSTL).

Verify intent in files/selection/best_method.py — look for method_overrides usage.

-- ClickHouse: check backtest metrics for a fully-zero intermittent series
SELECT method,
       round(avg_mase, 4)  AS mase,
       round(avg_smape, 4) AS smape,
       round(avg_bias, 4)  AS bias,
       n_windows
FROM PIPE_series_backtest_metrics
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND unique_id   = '4_305'
  AND forecast_origin = '__AGG__'
GROUP BY method, avg_mase, avg_smape, avg_bias, n_windows
ORDER BY method;

Test 8 — No orphan series (every scored series has a forecast)

Objective: Every unique_id in PIPE_series_best_methods must have at least one row in PIPE_forecast_point_values for its best_method.

-- ClickHouse: orphan check — best method with no forecast rows
SELECT bm.unique_id, bm.best_method
FROM PIPE_series_best_methods bm
WHERE bm.pipeline_id = 4
  AND bm.scenario_id = 4
  AND NOT EXISTS (
      SELECT 1
      FROM PIPE_forecast_point_values f
      WHERE f.pipeline_id = bm.pipeline_id
        AND f.scenario_id = bm.scenario_id
        AND f.unique_id   = bm.unique_id
        AND f.method      = bm.best_method
  )
ORDER BY bm.unique_id
LIMIT 20;

Pass criteria: 0 rows returned

Test 9 — Duplicate backtest rows do not skew scoring

Objective: PIPE_series_backtest_metrics should have exactly one __AGG__ row per (unique_id, method) pair. Duplicates inflate or deflate the composite score.

-- ClickHouse: find duplicate __AGG__ rows
SELECT unique_id, method, count() AS row_count
FROM PIPE_series_backtest_metrics
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND forecast_origin = '__AGG__'
GROUP BY unique_id, method
HAVING row_count > 1
ORDER BY row_count DESC
LIMIT 20;

Pass criteria: 0 rows returned

Observed issue (2026-05-04): Series 1_301 has 3 MSTL __AGG__ rows with slightly different metrics. This can happen when the backtest step is re-run without clearing previous results. If duplicates exist, best_method scoring takes an arbitrary one.

Remediation if duplicates found: 

-- ClickHouse: count affected series
SELECT count(DISTINCT unique_id) AS affected_series
FROM PIPE_series_backtest_metrics
WHERE pipeline_id=4 AND scenario_id=4 AND forecast_origin='__AGG__'
GROUP BY unique_id, method HAVING count() > 1;
Then re-run backtest with --force-full to regenerate clean metrics, or ALTER TABLE PIPE_series_backtest_metrics DELETE WHERE ... and re-run.

Test 10 — Backtest coverage is sufficient (>= 3 rolling windows)

Objective: A composite score built on 1 or 2 windows is statistically unreliable. Require at least 3 windows for the score to be trusted.

-- ClickHouse: series with fewer than 3 backtest windows
SELECT bm.unique_id, bm.best_method,
       m.n_windows,
       sc.n_observations
FROM PIPE_series_best_methods bm
JOIN PIPE_series_backtest_metrics m
    ON  bm.unique_id   = m.unique_id
    AND bm.pipeline_id = m.pipeline_id
    AND bm.scenario_id = m.scenario_id
    AND bm.best_method = m.method
    AND m.forecast_origin = '__AGG__'
JOIN PIPE_series_characteristics sc
    ON  bm.unique_id = sc.unique_id
    AND bm.pipeline_id = sc.pipeline_id
    AND bm.scenario_id = sc.scenario_id
WHERE bm.pipeline_id = 4
  AND bm.scenario_id = 4
  AND m.n_windows < 3
ORDER BY m.n_windows, sc.n_observations
LIMIT 20;

Pass criteria: Any result here is a warning — short series with few windows should be flagged; consider increasing n_observations minimum in the characterization step.

Known issues / flags

# Issue Severity Status
1 best_score = 0.0 for series 1_342 despite non-zero metrics Medium Investigate — may be a normalisation edge-case when all methods have near-equal MAE
2 Duplicate __AGG__ backtest rows for 1_301 (3 MSTL rows) Medium Re-run backtest with --force-full to clean up
3 Intermittent series (item 4) winning with MSTL despite method_overrides.intermittent='AutoTheta' Low Verify whether method_overrides is a forced-win or a fallback-only config
4 best_method config seasonal group still shows old methods (AutoETS, NHITS, PatchTST, TimesFM) Low This is the best_method scoring param, not the forecasting param — different table. Scoring does not filter by group, it scores all methods that ran.

How to drive the test

  1. Confirm weights — run the Test 1 SQL against PostgreSQL
  2. Run Tests 1–3 against ClickHouse — quick sanity on distribution
  3. Run Tests 4–5 — manual head-to-head metric check; confirm winner is mathematically correct
  4. Run Tests 6, 8 — structural integrity (runner-up present, no orphans)
  5. Run Test 9 — duplicate check before trusting any score
  6. Run Test 10 — low-window warning scan

Re-run best-method after any forecast or backtest change: 

python run_pipeline.py --only best-method --pipeline-id 4

Re-run backtest (if duplicates found): 

python run_pipeline.py --only backtest --pipeline-id 4 --force-full-run