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.

ranked_metric_pipeline.sql 
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 ()), 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 ()), 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 ()), 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;
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.