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.

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