Capstone Workflows

Customer Orders

Clean, Join, Summarize

A practical reporting workflow can clean dimension rows, join facts, summarize totals, and filter the final report.

Program

Play the script to choose the minimum customer total for the capstone order report.

min_total
customer_order_capstone.sql
Replay: real traced execution (multi-file project)
CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);
INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);
WITH params(min_total) AS (VALUES (50)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;

CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);
INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);
WITH params(min_total) AS (VALUES (30)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;

CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);
INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);
WITH params(min_total) AS (VALUES (100)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');
    values this step1 rowtables

  2. customers ← 3 rows

    1CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
    values this step3 rowscustomers

  3. tables ← 2 rows

    2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);
    values this step2 rowstables

  4. orders ← 4 rows

    3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);5WITH params(min_total) AS (VALUES (50)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;
    values this step4 rowsorders

  5. result ← 1 row

    4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);5WITH params(min_total) AS (VALUES (50)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');
    values this step1 rowtables

  2. customers ← 3 rows

    1CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
    values this step3 rowscustomers

  3. tables ← 2 rows

    2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);
    values this step2 rowstables

  4. orders ← 4 rows

    3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);5WITH params(min_total) AS (VALUES (30)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;
    values this step4 rowsorders

  5. result ← 2 rows

    4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);5WITH params(min_total) AS (VALUES (30)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');
    values this step1 rowtables

  2. customers ← 3 rows

    1CREATE TABLE customers (id INTEGER, name TEXT, status TEXT);2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
    values this step3 rowscustomers

  3. tables ← 2 rows

    2INSERT INTO customers VALUES (1, ' Ada ', 'active'), (2, 'Lin', 'active'), (3, 'Mira', 'paused');3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);
    values this step2 rowstables

  4. orders ← 4 rows

    3CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);5WITH params(min_total) AS (VALUES (100)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;
    values this step4 rowsorders

  5. result ← 1 row

    4INSERT INTO orders VALUES (101, 1, 40), (102, 1, 80), (103, 2, 30), (104, 3, 90);5WITH params(min_total) AS (VALUES (100)), clean_customers AS (SELECT id, trim(name) AS name FROM customers WHERE status = 'active'), customer_totals AS (SELECT clean_customers.name, SUM(orders.total) AS order_total FROM clean_customers JOIN orders ON orders.customer_id = clean_customers.id GROUP BY clean_customers.name), qualified AS (SELECT name, order_total FROM customer_totals WHERE order_total >= (SELECT min_total FROM params)) SELECT name, order_total FROM qualified ORDER BY order_total DESC, name;
    values this step1 rowresult
clean dimension `clean_customers` trims names and keeps active customers.
fact join `customer_totals` joins orders to the cleaned customer rows.
final threshold `qualified` applies the selector to the summarized totals.