# Draw ROC and lift curves

Model / evaluate

A variant of [Evaluate a model](https://meridian.online/operations/evaluate-a-model).

Rank rows by score and report gain, lift or ROC points at each cut-off.

Page: <https://meridian.online/operations/evaluate-a-model/draw-roc-and-lift-curves>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

rank rows by score and report gain, lift and ROC points at each cut-off

```sql
-- ten scored rows: a score and whether the row was truly positive; 0.80 is a tie between a negative and a positive
CREATE TEMP TABLE scored AS SELECT * FROM (VALUES
  (1, 0.95, 1), (2, 0.90, 1), (3, 0.80, 0), (4, 0.80, 1), (5, 0.70, 1), (6, 0.60, 0), (7, 0.50, 1), (8, 0.40, 0), (9, 0.30, 0), (10, 0.20, 0)) t(id, score, actual);
-- one row per distinct score: flag every row scoring at least the cut-off, so tied rows enter together
CREATE TEMP TABLE cutoffs AS
SELECT score AS cutoff,
       sum(sum(actual)) OVER w AS tp, sum(sum(1 - actual)) OVER w AS fp, sum(count(*)) OVER w AS flagged
FROM scored GROUP BY score WINDOW w AS (ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
CREATE TEMP TABLE curve AS
SELECT cutoff, tp::INTEGER AS tp, fp::INTEGER AS fp,
       tp / (SELECT sum(actual) FROM scored) AS gain,                      -- share of all positives found (the true positive rate)
       fp / (SELECT sum(1 - actual) FROM scored) AS fpr,
       flagged / (SELECT count(*) FROM scored) AS share_flagged,
       (tp / flagged) / ((SELECT sum(actual) FROM scored) / (SELECT count(*) FROM scored)) AS lift   -- precision over the base rate
FROM cutoffs;
SELECT cutoff, tp, fp, round(gain, 3) AS gain, round(fpr, 3) AS fpr, round(share_flagged, 2) AS share_flagged, round(lift, 3) AS lift FROM curve ORDER BY cutoff DESC;
-- area under the ROC curve by the trapezoid rule, starting from the origin (0, 0), and by counting positive-negative pairs the positive wins (a tie counts half)
CREATE TEMP TABLE area AS
WITH steps AS (SELECT fpr - coalesce(lag(fpr) OVER o, 0) AS width, gain + coalesce(lag(gain) OVER o, 0) AS height FROM curve WINDOW o AS (ORDER BY cutoff DESC))
SELECT (SELECT sum(width * height / 2) FROM steps) AS trapezoid,
       (SELECT sum(CASE WHEN p.score > n.score THEN 1.0 WHEN p.score = n.score THEN 0.5 ELSE 0 END) / (count(DISTINCT p.id) * count(DISTINCT n.id))
        FROM scored p, scored n WHERE p.actual = 1 AND n.actual = 0) AS pairs;
SELECT * FROM area;
SELECT CASE WHEN ((SELECT count(*) = 9 AND list([tp, fp] ORDER BY cutoff DESC) = [[1, 0], [2, 0], [3, 1], [4, 1], [4, 2], [5, 2], [5, 3], [5, 4], [5, 5]]
    AND list(round(gain, 3) ORDER BY cutoff DESC) = [0.2, 0.4, 0.6, 0.8, 0.8, 1.0, 1.0, 1.0, 1.0]
    AND list(round(fpr, 3) ORDER BY cutoff DESC) = [0.0, 0.0, 0.2, 0.2, 0.4, 0.4, 0.6, 0.8, 1.0]
    AND list(round(lift, 3) ORDER BY cutoff DESC) = [2.0, 2.0, 1.5, 1.6, 1.333, 1.429, 1.25, 1.111, 1.0] FROM curve)
  AND (SELECT abs(trapezoid - 0.86) < 1e-12 AND abs(pairs - 0.86) < 1e-12 FROM area)) THEN 'as expected' ELSE error('the curve should have 9 cut-offs from 0.95 to 0.20 with gain reaching 1 at 0.50, lift 2 at the first two, and an area of 0.86 by the trapezoid and by pairs') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`Lift Chart`](https://help.alteryx.com/current/en/designer/tools/predictive/lift-chart-tool.html) (read 2026-09-27)
- KNIME Analytics Platform: [`Enrichment Plotter (legacy)`](https://api.hub.knime.com/nodes/*GxxcUkRZEsQRVicB?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Lift Chart (JavaScript) (legacy)`](https://api.hub.knime.com/nodes/*QKVni78elpxYcMm0?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Lift Chart (legacy)`](https://api.hub.knime.com/nodes/*4DFM_cH5wpBaPN9o?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`ROC Curve`](https://api.hub.knime.com/nodes/*fXnxzvmq51oIA9Ou?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`ROC Curve (JavaScript) (legacy)`](https://api.hub.knime.com/nodes/*OKRvXgFKIQmsm76N?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`ROC Curve (legacy)`](https://api.hub.knime.com/nodes/*e3rnMYIl5BZphzl1?details=full) (read 2026-09-27)
- RapidMiner Studio: [`Compare ROCs`](https://docs.rapidminer.com/latest/studio/operators/validation/visual/compare_rocs.html) (read 2026-09-27)
- RapidMiner Studio: [`Create Lift Chart`](https://docs.rapidminer.com/latest/studio/operators/validation/visual/create_lift_chart.html) (read 2026-09-27)
- RapidMiner Studio: [`Lift Chart (Simple)`](https://docs.rapidminer.com/latest/studio/operators/validation/visual/lift_chart.html) (read 2026-09-27)