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.

min_spend
view_join.sql
Replay: real traced execution (multi-file project)
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 (15)) 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 (10)) 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 (20)) 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;

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
    values this step2 rowstables

  3. customers ← 3 rows

    2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);
    values this step3 rowscustomers

  4. purchases ← 4 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;
    values this step4 rowspurchases

  5. views ← 1 row

    4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;6WITH params(min_spend) AS (VALUES (15)) 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;
    values this step1 rowviews

  6. result ← 2 rows

    5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;6WITH params(min_spend) AS (VALUES (15)) 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;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
    values this step2 rowstables

  3. customers ← 3 rows

    2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);
    values this step3 rowscustomers

  4. purchases ← 4 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;
    values this step4 rowspurchases

  5. views ← 1 row

    4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;6WITH params(min_spend) AS (VALUES (10)) 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;
    values this step1 rowviews

  6. result ← 2 rows

    5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;6WITH params(min_spend) AS (VALUES (10)) 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;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
    values this step2 rowstables

  3. customers ← 3 rows

    2CREATE TABLE purchases (customer_id INTEGER, amount INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);
    values this step3 rowscustomers

  4. purchases ← 4 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;
    values this step4 rowspurchases

  5. views ← 1 row

    4INSERT INTO purchases VALUES (1, 12), (1, 8), (2, 15), (3, 4);5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;6WITH params(min_spend) AS (VALUES (20)) 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;
    values this step1 rowviews

  6. result ← 1 row

    5CREATE VIEW customer_spend AS SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id;6WITH params(min_spend) AS (VALUES (20)) 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;
    values this step1 rowresult

Follow the Joined View

  1. Purchases total to Ada 20, Lin 15, and Mia 4.
  2. The customer_spend view stores those totals by customer id.
  3. The query joins the view to customer names.
  4. The default min_spend is 15.
  5. Ada 20 and Lin 15 pass, so the output rows are Ada | 20 then Lin | 15. | customer | spend total | kept at 15? | | --- | --- | --- | | Ada | 20 | yes | | Lin | 15 | yes | | Mia | 4 | no |
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.

Exercise: view_join.sql

Reproduce the default rows Ada | 20 and Lin | 15, then use the pinned min_spend variants 10 and 20 to predict the same two rows, and then only Ada | 20.