Analytical CTE Pipelines

Clean and Group

Stage an Analysis

A CTE pipeline can name each analytical step: clean raw rows, filter accepted rows, then group the result.

Program

Play the script to choose the minimum accepted amount and see the grouped regional totals.

clean_group_pipeline.sql 
CREATE TABLE raw_sales (region TEXT, amount_text TEXT);
INSERT INTO raw_sales VALUES (' West ', '40'), ('East', 'bad'), ('West', '70'), (' East ', '30');
WITH params(min_amount) AS (VALUES ()), cleaned AS (SELECT trim(region) AS region, amount_text FROM raw_sales WHERE amount_text NOT GLOB '*[^0-9]*'), accepted AS (SELECT region, CAST(amount_text AS INTEGER) AS amount FROM cleaned WHERE CAST(amount_text AS INTEGER) >= (SELECT min_amount FROM params)), grouped AS (SELECT region, SUM(amount) AS total_amount, COUNT(*) AS sale_count FROM accepted GROUP BY region) SELECT region, total_amount, sale_count FROM grouped ORDER BY region;

CREATE TABLE raw_sales (region TEXT, amount_text TEXT);
INSERT INTO raw_sales VALUES (' West ', '40'), ('East', 'bad'), ('West', '70'), (' East ', '30');
WITH params(min_amount) AS (VALUES ()), cleaned AS (SELECT trim(region) AS region, amount_text FROM raw_sales WHERE amount_text NOT GLOB '*[^0-9]*'), accepted AS (SELECT region, CAST(amount_text AS INTEGER) AS amount FROM cleaned WHERE CAST(amount_text AS INTEGER) >= (SELECT min_amount FROM params)), grouped AS (SELECT region, SUM(amount) AS total_amount, COUNT(*) AS sale_count FROM accepted GROUP BY region) SELECT region, total_amount, sale_count FROM grouped ORDER BY region;
cleaned CTE `cleaned` trims the region and keeps numeric amount text.
accepted CTE `accepted` casts valid rows and applies the selector threshold.
grouped CTE `grouped` turns accepted detail rows into regional totals.