Views and Derived Tables

View Join

Reusing a Summary

A view can be joined with another table. This keeps the summary logic in one place and the lookup logic in another.

Program

Play the script to join customer names to a reusable spend summary.

view_join.sql 
CREATE TABLE customers (id INTEGER, name TEXT);
CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);
CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;
WITH params(min_spend) AS (VALUES ()) SELECT customers.name, customer_spend.total FROM customers JOIN customer_spend ON customers.id = customer_spend.customer_id WHERE customer_spend.total >= (SELECT min_spend FROM params) ORDER BY customer_spend.total DESC;

CREATE TABLE customers (id INTEGER, name TEXT);
CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);
CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;
WITH params(min_spend) AS (VALUES ()) SELECT customers.name, customer_spend.total FROM customers JOIN customer_spend ON customers.id = customer_spend.customer_id WHERE customer_spend.total >= (SELECT min_spend FROM params) ORDER BY customer_spend.total DESC;

CREATE TABLE customers (id INTEGER, name TEXT);
CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);
CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;
WITH params(min_spend) AS (VALUES ()) SELECT customers.name, customer_spend.total FROM customers JOIN customer_spend ON customers.id = customer_spend.customer_id WHERE customer_spend.total >= (SELECT min_spend FROM params) ORDER BY customer_spend.total DESC;
view reuse `customer_spend` packages the grouped purchase summary.
JOIN `JOIN customer_spend ON ...` combines names with totals.
threshold Changing `min_spend` changes which joined summary rows remain.