Analytical CTE Pipelines

Metric Pipeline

Rank Derived Scores

A pipeline can derive a metric, rank it, and return only rows that pass a final rank filter.

Program

Play the script to choose how many ranked metric rows are shown.

max_rank
ranked_metric_pipeline.sql
Replay: real traced execution (multi-file project)
CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);
INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);
WITH params(max_rank) AS (VALUES (2)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;

CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);
INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);
WITH params(max_rank) AS (VALUES (1)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;

CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);
INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);
WITH params(max_rank) AS (VALUES (3)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;

  1. tables ← 1 row

    1CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);
    values this step1 rowtables

  2. feature_metrics ← 4 rows

    1CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);3WITH params(max_rank) AS (VALUES (2)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;
    values this step4 rowsfeature_metrics

  3. result ← 2 rows

    2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);3WITH params(max_rank) AS (VALUES (2)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);
    values this step1 rowtables

  2. feature_metrics ← 4 rows

    1CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);3WITH params(max_rank) AS (VALUES (1)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;
    values this step4 rowsfeature_metrics

  3. result ← 1 row

    2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);3WITH params(max_rank) AS (VALUES (1)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);
    values this step1 rowtables

  2. feature_metrics ← 4 rows

    1CREATE TABLE feature_metrics (feature TEXT, users INTEGER, errors INTEGER);2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);3WITH params(max_rank) AS (VALUES (3)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;
    values this step4 rowsfeature_metrics

  3. result ← 3 rows

    2INSERT INTO feature_metrics VALUES ('search', 120, 3), ('checkout', 80, 5), ('profile', 50, 1), ('reports', 30, 0);3WITH params(max_rank) AS (VALUES (3)), scored AS (SELECT feature, users, errors, users - errors * 10 AS health_score FROM feature_metrics), ranked AS (SELECT feature, users, errors, health_score, ROW_NUMBER() OVER (ORDER BY health_score DESC, feature) AS metric_rank FROM scored) SELECT feature, health_score, metric_rank FROM ranked WHERE metric_rank <= (SELECT max_rank FROM params) ORDER BY metric_rank;
    values this step3 rowsresult
scored CTE `scored` derives a health score from fixed metric columns.
ranked CTE `ranked` assigns a stable rank using the derived score.
final filter The selector controls how many ranked rows the report returns.