Skip to content

ABC Classification — 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: Two active configurations — ABC (demand / cumulative_pct) and XYZ (hits / rank_pct)

TOC

  1. Configurations overview
  2. Test 1 — Configurations are active and complete
  3. Test 2 — Total series coverage
  4. Test 3 — Class distribution matches thresholds (ABC config)
  5. Test 4 — Boundary correctness A/B and B/C
  6. Test 5 — Cumulative % math is correct
  7. Test 6 — Ranking is strictly monotone (no ties skew)
  8. Test 7 — Top series are plausible (spot-check)
  9. Test 8 — XYZ config (hits / rank_pct)
  10. Test 9 — Cross-config consistency (same universe)
  11. Test 10 — No series classified in multiple classes
  12. Known issues / flags
  13. How to drive the test

Configurations overview

Two active configurations in scenario.config_abc_configuration:

id name metric method lookback thresholds labels
1 Default (demand) demand cumulative_pct 12 months [80, 95] A, B, C
23 hits hits rank_pct 12 months [80.0, 95.0] X, Y, Z

Config 1 (ABC): Sorts series by 12-month demand volume descending. Assigns A to series whose cumulative demand reaches 80% of the total, B to the next 15%, C to the remainder.

Config 23 (XYZ): Sorts series by demand frequency (number of weeks with qty > 0) over 12 months. Uses rank_pct (rank relative to total series count) rather than cumulative volume.

Verify configurations:

-- PostgreSQL (thebicycle)
SELECT id, name, metric, method, lookback_months,
       thresholds, class_labels, is_active, granularity
FROM scenario.config_abc_configuration
ORDER BY id;

Test 1 — Configurations are active and complete

Objective: Both configs are flagged is_active=TRUE and have non-empty thresholds.

-- PostgreSQL
SELECT id, name, is_active,
       array_length(thresholds, 1) AS n_thresholds,
       array_length(class_labels, 1) AS n_labels
FROM scenario.config_abc_configuration
ORDER BY id;

Pass criteria: - is_active = TRUE for both rows - n_thresholds = 2 (two cut points define three classes) - n_labels = 3 - n_thresholds = n_labels - 1 always

Test 2 — Total series coverage

Objective: Both configs classify the same number of series. The only legitimate exclusion is a series with zero demand across the lookback window.

-- ClickHouse
SELECT config_id, count(DISTINCT unique_id) AS series_classified
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
GROUP BY config_id
ORDER BY config_id;

Observed 2026-05-04:

config_id series_classified
1 575
23 575

Total series in PIPE_series_characteristics: 576

-- ClickHouse: which series are missing from config 1?
SELECT unique_id
FROM PIPE_series_characteristics
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND unique_id NOT IN (
      SELECT unique_id FROM PIPE_abc_results
      WHERE pipeline_id = 4
        AND scenario_id = 4
        AND config_id   = 1
  );

Observed: 25_312 — this series has zero demand in master.demand_actuals (total qty = NULL, 0 rows). Correct to exclude; a series with no demand has no rank in a demand-volume classification.

Pass criteria: - Both configs cover the same series count - Any missing series must have zero demand (verify with the query above)

Test 3 — Class distribution matches thresholds (ABC config)

Objective: With thresholds [80, 95], the cumulative percentage at the last A-series must be ≤ 80%, the first B-series ≥ 80%, the last B ≤ 95%, the first C ≥ 95%.

-- ClickHouse
SELECT class_label,
       count()                           AS series_count,
       round(min(cumulative_pct), 2)     AS cum_pct_first,
       round(max(cumulative_pct), 2)     AS cum_pct_last
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND config_id   = 1
GROUP BY class_label
ORDER BY class_label;

Observed 2026-05-04:

class series_count cum_pct_first cum_pct_last
A 322 1.18 79.94
B 180 80.03 94.94
C 73 95.01 100.00

Pass criteria: - A.cum_pct_last ≤ 80 and B.cum_pct_first > 80 - B.cum_pct_last ≤ 95 and C.cum_pct_first > 95 - C.cum_pct_last = 100.00 (all demand accounted for) - Series counts sum to total classified (322+180+73 = 575)

Test 4 — Boundary correctness A/B and B/C

Objective: The series immediately before and after each class boundary are correctly assigned. A series with cumulative_pct=79.94 is A; the next (80.03) is B.

-- ClickHouse: A/B boundary (last 3 A + first 3 B)
SELECT unique_id, class_label,
       round(metric_value,   1) AS demand_12m,
       round(cumulative_pct, 2) AS cum_pct,
       rank
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND config_id   = 1
  AND cumulative_pct BETWEEN 79.5 AND 80.5
ORDER BY rank;

Expected: class flips from A → B exactly where cumulative_pct crosses 80.

-- ClickHouse: B/C boundary (last 3 B + first 3 C)
SELECT unique_id, class_label,
       round(metric_value,   1) AS demand_12m,
       round(cumulative_pct, 2) AS cum_pct,
       rank
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND config_id   = 1
  AND cumulative_pct BETWEEN 94.5 AND 95.5
ORDER BY rank;

Observed A/B boundary (2026-05-04):

unique_id class demand_12m cum_pct
12_315 A 74.1 79.94
15_314 B 74.1 80.03

Both have identical demand_12m = 74.1 — a tie at the threshold. The assignment is deterministic by rank (rank order breaks ties). No series is misclassified.

Observed B/C boundary:

unique_id class demand_12m cum_pct
17_301 B 57.2 94.94
15_332 C 57.0 95.01

Clean drop in demand value across the boundary — consistent.

Test 5 — Cumulative % math is correct

Objective: Verify cumulative_pct at rank 1 equals metric_value_rank1 / total_demand * 100.

-- ClickHouse: verify rank-1 cumulative pct
SELECT
    round(metric_value, 1)   AS rank1_demand,
    round(cumulative_pct, 4) AS stored_cum_pct,
    round(
        metric_value / (
            SELECT sum(metric_value)
            FROM PIPE_abc_results
            WHERE pipeline_id = 4 AND scenario_id = 4 AND config_id = 1
        ) * 100
    , 4) AS computed_cum_pct
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND config_id   = 1
  AND rank        = 1;

Observed 2026-05-04: - rank1_demand = 937.0 - stored_cum_pct = 1.18 - computed_cum_pct = 1.18 (937 / 79072.2 × 100 = 1.185%)

Pass criteria: abs(stored_cum_pct - computed_cum_pct) < 0.05

Note — total vs demand_actuals mismatch (2026-05-04): ABC total (config 1) = 79,072 vs demand_actuals last-12-month sum = 75,103 (~5% gap). This is expected: ABC uses a lookback of exactly 53 weeks (≈ 12 months), which includes slightly more data than a calendar-12-month window. Outlier corrections may also shift the ABC total if the pipeline applies corrected values instead of raw actuals.

To confirm which demand source the ABC step reads: 

-- PostgreSQL: check if demand_corrected is used
SELECT parameters_set->'abc'->'use_corrected_demand' AS use_corrected
FROM scenario.config_parameters
WHERE parameter_type = 'abc' AND is_default = TRUE;

Test 6 — Ranking is strictly monotone (no ties skew)

Objective: cumulative_pct must be non-decreasing across all ranks. A violation means the ranking is broken (sort instability, duplicate insertion, etc.).

-- ClickHouse: monotone check — any row where cum_pct < previous row?
SELECT count() AS violations
FROM (
    SELECT rank,
           cumulative_pct,
           lagInFrame(cumulative_pct) OVER (ORDER BY rank) AS prev_cum_pct
    FROM PIPE_abc_results
    WHERE pipeline_id = 4
      AND scenario_id = 4
      AND config_id   = 1
)
WHERE cumulative_pct < prev_cum_pct;

Observed: 0 violations — ranking is clean.

Pass criteria: result = 0

-- ClickHouse: also check that rank values are contiguous (no gaps)
SELECT
    max(rank) - min(rank) + 1 AS expected_rows,
    count()                   AS actual_rows
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND config_id   = 1;

Pass criteria: expected_rows = actual_rows

Test 7 — Top series are plausible (spot-check)

Objective: Rank 1–5 should be recognisable high-volume SKU×site combinations. Chain Lube at major Netherlands cycling shops is a strong candidate for top-volume.

-- ClickHouse
SELECT r.unique_id,
       r.class_label,
       round(r.metric_value,   1) AS demand_12m,
       round(r.cumulative_pct, 2) AS cum_pct,
       r.rank
FROM PIPE_abc_results r
WHERE r.pipeline_id = 4
  AND r.scenario_id = 4
  AND r.config_id   = 1
ORDER BY r.rank
LIMIT 10;

Observed 2026-05-04 (names resolved via master.item / master.location):

rank unique_id class demand_12m cum_pct item site
1 1_342 A 937.0 1.18% Chain Lube Finish Line 500ml Rotterdam Fietsen
2 1_344 A 886.3 2.31% Chain Lube Finish Line 500ml Eindhoven Bikes
3 1_301 A 774.0 3.28% Chain Lube Finish Line 500ml Cycles Paris
4 1_313 A 773.2 4.26% Chain Lube Finish Line 500ml Hamburg Fahrrad
5 1_341 A 746.8 5.21% Chain Lube Finish Line 500ml Amsterdam Fietsen

Top 5 are all the same item (Chain Lube) at different sites — plausible for a consumable with high weekly demand. Combined, they account for 5.21% of total demand.

Flag: item_name and site_name columns in PIPE_abc_results are stored empty. Names are resolved at query time via master.item / master.location joins. The ABC write step does not populate the denormalised name columns. See Known issues item 1.

Test 8 — XYZ config (hits / rank_pct)

Objective: XYZ uses demand frequency (weeks with qty > 0) as the metric. Thresholds [80, 95] split by rank percentile — the top 80% of series by hit count are X, next 15% are Y, bottom 5% are Z.

-- ClickHouse: hit count distribution
SELECT toInt32(metric_value) AS hit_weeks,
       count()               AS series_count
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND config_id   = 23
GROUP BY hit_weeks
ORDER BY hit_weeks DESC;

Observed 2026-05-04:

hit_weeks series_count
53 571
52 4

Flag — degenerate distribution (2026-05-04): 571 of 575 series have identical hit count = 53 (100% demand weeks). Only 4 series have 52 hits. With rank_pct method the tie-breaking is by internal sort order — 460 X, 87 Y, 28 Z classes result from rank position alone, not from meaningful metric differentiation.

This configuration is not useful in its current state: nearly all series have essentially the same hit count, so XYZ is arbitrary. Consider switching to a metric that differentiates better (e.g. ADI, CV, zero_ratio) or extending the lookback to 24 months to expose more variance.

-- ClickHouse: XYZ class distribution
SELECT class_label,
       count()                       AS series_count,
       round(min(cumulative_pct), 2) AS cum_pct_start,
       round(max(cumulative_pct), 2) AS cum_pct_end,
       round(min(metric_value),   0) AS min_hits,
       round(max(metric_value),   0) AS max_hits
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND config_id   = 23
GROUP BY class_label
ORDER BY class_label;
class series_count cum_pct [start–end] hits range
X 460 0.17% – 80.01% 53–53
Y 87 80.18% – 95.14% 53–53
Z 28 95.32% – 100.00% 52–53

Test 9 — Cross-config consistency (same universe)

Objective: Both configs must classify the exact same set of series. A mismatch indicates one config was run against a different demand snapshot.

-- ClickHouse: series in config 1 but not config 23
SELECT unique_id
FROM PIPE_abc_results
WHERE pipeline_id = 4 AND scenario_id = 4 AND config_id = 1
  AND unique_id NOT IN (
      SELECT unique_id FROM PIPE_abc_results
      WHERE pipeline_id = 4 AND scenario_id = 4 AND config_id = 23
  );

-- ClickHouse: series in config 23 but not config 1
SELECT unique_id
FROM PIPE_abc_results
WHERE pipeline_id = 4 AND scenario_id = 4 AND config_id = 23
  AND unique_id NOT IN (
      SELECT unique_id FROM PIPE_abc_results
      WHERE pipeline_id = 4 AND scenario_id = 4 AND config_id = 1
  );

Observed: 0 rows in both queries — perfect alignment.

Pass criteria: both queries return 0 rows

Test 10 — No series classified in multiple classes

Objective: A (config_id, unique_id) pair must have exactly one class label. Duplicate insertion (e.g., from re-running without clearing) would cause double counting.

-- ClickHouse: find duplicate class assignments
SELECT config_id, unique_id, count(DISTINCT class_label) AS n_classes
FROM PIPE_abc_results
WHERE pipeline_id = 4
  AND scenario_id = 4
GROUP BY config_id, unique_id
HAVING n_classes > 1
ORDER BY n_classes DESC
LIMIT 20;

Pass criteria: 0 rows returned

Known issues / flags

# Issue Severity Status
1 item_name and site_name columns in PIPE_abc_results are empty — ABC write step does not populate denormalised name columns Low Resolve via JOINs at query time; or fix the ABC writer to populate names same as other PIPE_ tables
2 segment_name is empty for all rows — series are not assigned to named segments Low Investigate whether segment assignment runs before or after ABC; check PIPE_segment_membership join logic
3 XYZ config (id=23) is degenerate: 571/575 series have identical hit count = 53, so rank_pct assigns classes by tie-breaking order, not by meaningful frequency spread Medium Consider replacing hits metric with ADI or zero_ratio, or extending lookback to 24 months
4 ABC total demand (79,072) is ~5% higher than demand_actuals 12-month sum (75,103) — ABC may use 53-week lookback vs calendar-12-month, or uses outlier-corrected values Low Verify which demand source and exact date range the ABC step reads
5 Series 25_312 is absent from both configs — it has zero demand. Correct behaviour, but it remains in PIPE_series_characteristics. Confirm it is intentionally orphaned or should be excluded from characterisation too Low Review

How to drive the test

  1. Verify configs — run Test 1 SQL against PostgreSQL
  2. Coverage check — run Test 2 to confirm 575/576 and identify the zero-demand exclusion
  3. Boundary check — run Test 4 to confirm class assignments at the 80% and 95% thresholds
  4. Math check — run Test 5 to verify cumulative % at rank 1
  5. Monotone check — run Test 6 (should be 0 violations after any re-run)
  6. Duplicate check — run Test 10 before trusting any downstream segmentation using ABC classes
  7. XYZ review — run Test 8 hit distribution; if still degenerate, consider reconfiguring config 23

Re-run ABC classification: 

ProcessRunner → Classification  (or via API: POST /api/abc/run/{config_id})

Check downstream usage of ABC classes — segments, parameter assignment, MEIO may use class_label from PIPE_abc_results to route SKUs to parameter sets. Degenerate XYZ classification (issue 3) propagates silently to those downstream steps.