Views and CTEs

As your queries grow more complex, you need ways to organize them. Views and Common Table Expressions (CTEs) are two powerful tools for breaking complex logic into manageable, reusable pieces. They make your SQL easier to read, maintain, and debug.

What You'll Learn

By the end of this lesson you will give a name to a piece of query logic and then build on that name — with a view, which the database keeps, and with a CTE, which lasts one statement. The judgment you are practising is which of the two a piece of logic deserves, and it comes down to one question: does anything else need to read this?

This is the milestone the capstone's analytics stand on — the final project has to expose a named view over its order data so that its reports read as questions rather than as re-derivations. You arrive here able to nest one query inside another and feed its answer to the outer one (Subqueries); what is new is naming those inner queries so they read top to bottom instead of inside out.

Creating Views

A view is a saved query that acts like a virtual table. It doesn't store data — it runs the underlying query each time you reference it.

-- Create base tables
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary INTEGER,
    hire_date TEXT
);

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    location TEXT
);

INSERT INTO departments (id, name, location)
VALUES
    (1, 'Engineering', 'Building A'),
    (2, 'Marketing', 'Building B'),
    (3, 'Sales', 'Building C');

INSERT INTO employees (id, name, department_id, salary, hire_date)
VALUES
    (1, 'Alice Chen', 1, 95000, '2020-03-15'),
    (2, 'Bob Martinez', 1, 88000, '2021-06-01'),
    (3, 'Carol White', 2, 72000, '2019-11-20'),
    (4, 'David Kim', 2, 68000, '2022-01-10'),
    (5, 'Eva Brown', 3, 65000, '2023-04-05'),
    (6, 'Frank Lee', 3, 71000, '2020-08-22'),
    (7, 'Grace Patel', 1, 102000, '2018-07-14');

-- Create a view that joins employees with departments
CREATE VIEW employee_details AS
SELECT
    e.id,
    e.name,
    d.name AS department,
    d.location,
    e.salary,
    e.hire_date
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- Now query the view like a table
SELECT * FROM employee_details;

-- Filter and sort the view
SELECT name, department, salary
FROM employee_details
WHERE salary > 80000
ORDER BY salary DESC;

Once a view exists, you can SELECT from it just like any table. You can filter, sort, join, and aggregate view results.

A view is read-only

The "just like a table" comparison stops at reading. A view stores no rows of its own — it is a saved query — so there is nothing in it to change, and writing to one is refused:

CREATE TABLE t (id INTEGER PRIMARY KEY, v INTEGER);
INSERT INTO t VALUES (1,10),(2,20);
CREATE VIEW vv AS SELECT id, v FROM t WHERE v > 5;
SELECT id, v FROM vv ORDER BY id;
UPDATE vv SET v = 99 WHERE id = 1;

The SELECT works and prints both rows. The UPDATE does not:

Error: cannot modify vv because it is a view

To change the data, write to the underlying table instead. (There is a way to make a view accept writes, by attaching a trigger that redirects them to the base table, but triggers come much later in this track and a view is read-only until you build that yourself.)

Which raises the question the block below asks: if the view holds no rows of its own, what does it show you after the table underneath it changes?

CREATE TABLE readings (id INTEGER PRIMARY KEY, sensor TEXT NOT NULL, value INTEGER NOT NULL);
INSERT INTO readings VALUES (1,'a',10),(2,'b',20),(3,'a',30);
CREATE VIEW high_readings AS SELECT id, sensor, value FROM readings WHERE value >= 10;
SELECT COUNT(*) AS first_read FROM high_readings;
INSERT INTO readings VALUES (4,'c',40);
SELECT COUNT(*) AS second_read FROM high_readings;

Views for Simplifying Aggregations

Views are especially useful for saving complex aggregations that you query repeatedly.

-- Create tables
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    quantity INTEGER,
    order_date TEXT
);

INSERT INTO products (id, name, category, price)
VALUES
    (1, 'Laptop', 'Electronics', 999.99),
    (2, 'Mouse', 'Electronics', 24.99),
    (3, 'Desk', 'Furniture', 299.99),
    (4, 'Chair', 'Furniture', 199.99),
    (5, 'Keyboard', 'Electronics', 79.99);

INSERT INTO order_items (id, product_id, quantity, order_date)
VALUES
    (1, 1, 3, '2025-01-10'),
    (2, 2, 15, '2025-01-11'),
    (3, 3, 5, '2025-01-12'),
    (4, 1, 2, '2025-01-15'),
    (5, 4, 8, '2025-01-16'),
    (6, 2, 10, '2025-01-18'),
    (7, 5, 12, '2025-01-20');

-- Create a view that summarizes product sales
CREATE VIEW product_sales_summary AS
SELECT
    p.name AS product,
    p.category,
    p.price,
    SUM(oi.quantity) AS total_sold,
    ROUND(SUM(oi.quantity * p.price), 2) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category, p.price;

-- Query the summary view
SELECT * FROM product_sales_summary
ORDER BY total_revenue DESC;

-- Aggregate the view further
SELECT
    category,
    SUM(total_sold) AS category_units,
    SUM(total_revenue) AS category_revenue
FROM product_sales_summary
GROUP BY category;

-- Drop a view when no longer needed
-- DROP VIEW product_sales_summary;

Two variations to try on the fence above — open it with its Open in Playground pill, then edit and Run Code. Predict each before you run it.

1. Add ORDER BY category to the category aggregate, the second query. It prints Electronics at 42 units and 6584.58, then Furniture at 13 and 3099.87. The numbers are the same either way — what you are fixing is that a grouped query without an ORDER BY has no promised row order, so the report is only reproducible by luck. Do this to every report you intend to read twice.
2. Replace that second query with a filter over the view: SELECT product, total_revenue FROM product_sales_summary WHERE total_revenue > 1000 ORDER BY total_revenue DESC; Three rows come back — Laptop at 4999.95, Chair at 1599.92, Desk at 1499.95. Note what you did not have to write: the join, the grouping and the revenue arithmetic are all inside the name, so the filter reads like a question about products rather than a re-derivation.

A view is where a mistake becomes permanent, which is the argument for getting its definition exactly right. Every query written against the name inherits whatever the definition does — including what it silently leaves out.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_cents INTEGER NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL);
INSERT INTO products VALUES (1,'Laptop','Electronics',99900),(2,'Mouse','Electronics',2499),(3,'Desk','Furniture',29999),(4,'Lamp','Furniture',4999);
INSERT INTO order_items VALUES (1,1,3),(2,2,15),(3,1,2),(4,3,5);
CREATE VIEW product_revenue AS
SELECT p.id, p.name, p.category, SUM(oi.quantity * p.price_cents) AS revenue_cents
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name, p.category;
SELECT name, category, revenue_cents FROM product_revenue ORDER BY name;
SELECT COUNT(*) AS products_listed FROM product_revenue;

4 row(s) affected
4 row(s) affected
name | category | revenue_cents
-------------------------------
Desk | Furniture | 149995
Lamp | Furniture | 0
Laptop | Electronics | 499500
Mouse | Electronics | 37485
products_listed
---------------
4

Common Table Expressions (CTEs)

A CTE uses the WITH keyword to define a temporary named result set within a single query. Think of it as an inline, disposable view.

-- Create a sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    salesperson TEXT NOT NULL,
    region TEXT NOT NULL,
    amount REAL,
    sale_date TEXT
);

INSERT INTO sales (id, salesperson, region, amount, sale_date)
VALUES
    (1, 'Alice', 'North', 5000, '2025-01-05'),
    (2, 'Alice', 'North', 3200, '2025-01-12'),
    (3, 'Bob', 'South', 4500, '2025-01-08'),
    (4, 'Bob', 'South', 6100, '2025-01-15'),
    (5, 'Carol', 'North', 2800, '2025-01-10'),
    (6, 'Carol', 'North', 3900, '2025-01-18'),
    (7, 'David', 'South', 7200, '2025-01-06'),
    (8, 'David', 'South', 4800, '2025-01-20');

-- Use a CTE to find top performers per region
WITH salesperson_totals AS (
    SELECT
        salesperson,
        region,
        SUM(amount) AS total_sales,
        COUNT(*) AS num_deals
    FROM sales
    GROUP BY salesperson, region
),
region_averages AS (
    SELECT
        region,
        ROUND(AVG(total_sales), 2) AS avg_sales
    FROM salesperson_totals
    GROUP BY region
)
SELECT
    st.salesperson,
    st.region,
    st.total_sales,
    ra.avg_sales AS region_avg,
    CASE
        WHEN st.total_sales > ra.avg_sales THEN 'Above Average'
        ELSE 'Below Average'
    END AS performance
FROM salesperson_totals st
JOIN region_averages ra ON st.region = ra.region
ORDER BY st.region, st.total_sales DESC;

Two things in that query are new here and worth a sentence each, because they arrive from later lessons.

CASE WHEN condition THEN value ELSE other END is SQL's conditional expression: it evaluates the condition for each row and produces one of the two values, so the performance column holds the text Above Average or Below Average rather than a boolean. It is an expression, not a statement — it can appear anywhere a value can, including inside an aggregate. String Functions and Null Handling both lean on it heavily when they arrive.

The second is the chaining itself: region_averages is a CTE that reads from salesperson_totals, another CTE in the same WITH clause. That is what makes CTEs feel like a pipeline — each step names a result the next step can use, and the final SELECT reads the last one.

CTEs are defined with WITH cte_name AS (...). You can chain multiple CTEs separated by commas, and a CTE can reference another one in the same WITH clause. Ordering them so each reads only from the ones above it is a readability convention rather than a requirement — this engine resolves a reference to a CTE defined further down as well — but write them top to bottom anyway, because that order is what makes the query read as a pipeline.

Ordering a CTE pipeline

Every arrangement below runs against the sales table, which is already given.

CREATE TABLE sales (id INTEGER PRIMARY KEY, rep TEXT NOT NULL, region TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES (1,'Ada','north',5000),(2,'Ada','north',3200),(3,'Bo','south',4500),(4,'Bo','south',6100),(5,'Cy','north',2800);

Recursive CTEs

Recursive CTEs can reference themselves, making them perfect for hierarchical data like org charts, category trees, or sequences.

-- Create an employees table with a manager hierarchy
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER,
    title TEXT
);

INSERT INTO employees (id, name, manager_id, title)
VALUES
    (1, 'Sarah CEO', NULL, 'CEO'),
    (2, 'Tom VP Eng', 1, 'VP Engineering'),
    (3, 'Lisa VP Sales', 1, 'VP Sales'),
    (4, 'Mike Lead', 2, 'Engineering Lead'),
    (5, 'Anna Dev', 4, 'Senior Developer'),
    (6, 'James Dev', 4, 'Developer'),
    (7, 'Karen Sales', 3, 'Sales Manager'),
    (8, 'Chris Sales', 7, 'Sales Rep');

-- Recursive CTE to build the org chart
WITH RECURSIVE org_chart AS (
    -- Base case: start with the CEO (no manager)
    SELECT
        id,
        name,
        title,
        manager_id,
        0 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: find employees who report to someone already in the result
    SELECT
        e.id,
        e.name,
        e.title,
        e.manager_id,
        oc.level + 1,
        oc.path || ' > ' || e.name
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
    substr('            ', 1, level * 4) || name AS org_tree,
    title,
    level,
    path
FROM org_chart
ORDER BY path;

A recursive CTE has two parts:

1. Base case: the initial SELECT that doesn't reference the CTE
2. Recursive case: the SELECT that references the CTE, connected with UNION ALL

The database repeatedly runs the recursive part until it produces no new rows.

Three pieces of that query arrive from lessons you have not reached yet, so here is what each one is doing:

• UNION ALL stacks the rows of two SELECTs into one result, keeping duplicates. Plain UNION also works here and removes them; ALL is the conventional choice for a recursion because it skips the duplicate check, and because a hierarchy should not have duplicates to remove. Set Operations covers the whole family properly.
• || concatenates strings, which is how path accumulates Sarah CEO > Tom VP Eng > Mike Lead one level at a time. String Functions covers text manipulation properly.
• substr(string, start, length) takes a slice of a string, counting from 1. Here it slices a run of spaces to indent each name by its depth.

That last one has a limit worth knowing about before it bites you. The string being sliced is only twelve spaces long, and substr cannot return more characters than it was given, so the indent stops growing once level * 4 reaches twelve: level 3, level 4 and level 5 would all indent by the same twelve spaces. This hierarchy stops at level 3 — Chris Sales, Anna Dev and James Dev are the deepest rows, indented twelve spaces — so every level in the output above is still visibly distinct, and you cannot see the problem here. Add one more layer of reports under any of those three and the new rows line up underneath their own managers instead of below them. Widen the literal, or build the indent with a repeat function, if you need to go deeper than three levels.

The word RECURSIVE is also optional on this engine — WITH org_chart AS (...) with a self-reference inside works exactly the same way. Write it anyway: it tells the next reader that the query is recursive before they reach the part that proves it, and other engines require it.

Generating Sequences with Recursive CTEs

Recursive CTEs aren't just for hierarchies — they can generate data sequences too.

-- Generate a sequence of dates for the first two weeks of January
WITH RECURSIVE date_series AS (
    SELECT '2025-01-01' AS date
    UNION ALL
    SELECT date(date, '+1 day')
    FROM date_series
    WHERE date < '2025-01-14'
)
SELECT date,
    CASE CAST(strftime('%w', date) AS INTEGER)
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day_of_week
FROM date_series;

This technique is extremely useful for generating calendar tables, number sequences, or filling gaps in time-series data.

Two more borrowed pieces, one from Date and Time Functions and one from Data Types and Casting: date(x, '+1 day') does calendar arithmetic on a YYYY-MM-DD string, and strftime('%w', date) extracts the day of week — as text, which is why it is wrapped in CAST(... AS INTEGER) before being matched against the numbers 0 to 6. Drop the CAST and every branch of the CASE fails to match, because '3' and 3 are not equal here.

Views vs CTEs

Both simplify complex queries, but they serve different purposes:

Read the self-reference row carefully, because it is easy to overstate. A view cannot be recursive in the sense of referring to itself — its definition is resolved against the tables and views that exist, and naming itself inside its own body is not a thing you can do. But a view over a recursive CTE works perfectly well: CREATE VIEW org AS WITH RECURSIVE ... SELECT ... is a valid view, and every read of it re-runs the recursion. So recursion is not off-limits to views; self-reference is.

Try It Yourself

Build the reusable analysis layer the capstone needs: one named view that every report can read, and one CTE on top of it that answers a question the view alone cannot. The starter computes the per-customer totals inline and stops there.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, region TEXT NOT NULL, total_cents INTEGER NOT NULL);
INSERT INTO orders VALUES (1,'Ada','north',12000),(2,'Bo','south',8000),(3,'Ada','north',6000),(4,'Cy','south',20000),(5,'Bo','south',1500),(6,'Di','north',4000);
-- TODO 1: move the per-customer summary below into a view named
--         customer_totals, exposing customer, region, spent_cents and
--         orders_placed. Ada must come out at 18000 across 2 orders.
-- TODO 2: using that view, report only the customers whose spent_cents is
--         above the average spent_cents of their own region. Compute the
--         per-region average in a CTE named region_avg that reads from the
--         view. Sort by spent_cents descending, then customer ascending.
SELECT customer, region, SUM(total_cents) AS spent_cents, COUNT(*) AS orders_placed
FROM orders
GROUP BY customer, region
ORDER BY customer;

6 row(s) affected
customer | region | spent_cents | orders_placed
-----------------------------------------------
Cy | south | 20000 | 1
Ada | north | 18000 | 2

Once it passes, try two variations and predict each before you run it.

1. Change the comparison from greater-than to greater-than-or-equal. The output does not change at all. That is the informative result: it tells you no customer in this data sits exactly on their region's average, so the boundary case never fires. Checking an edge and finding it empty is a real answer, and it is cheaper than reasoning about which rows might be on it.
2. Drop the join to region_avg and compare against a single overall average instead, computed as a scalar subquery over the view. Cy and Ada still come back, but for a different reason — they now clear the average of all four customers rather than of their own region. Same rows here, different question, and on other data the two would disagree.

CREATE TABLE sales (id INTEGER PRIMARY KEY, rep TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES (1,'Ada',5000),(2,'Ada',3200),(3,'Bo',4500);

WITH totals AS (SELECT rep, SUM(amount) AS total FROM sales GROUP BY rep)
SELECT rep, total FROM totals WHERE total > 4000 ORDER BY rep;

CREATE VIEW totals_v AS SELECT rep, SUM(amount) AS total FROM sales GROUP BY rep;
SELECT rep, total FROM totals_v WHERE total > 4000 ORDER BY rep;

SELECT rep, total FROM (SELECT rep, SUM(amount) AS total FROM sales GROUP BY rep) WHERE total > 4000 ORDER BY rep;

INSERT INTO sales VALUES (4,'Bo',1000);
SELECT rep, total FROM totals_v WHERE total > 4000 ORDER BY rep;

Key Takeaways

• Views are saved queries that act like virtual tables — they store no rows, so they are always current, always cost what their query costs, and are read-only
• CTEs use WITH ... AS (...) to create temporary result sets within a single query
• You can chain multiple CTEs and have one read from another; writing them top to bottom is convention, not a requirement this engine enforces
• Recursive CTEs handle hierarchical data and sequence generation
• A recursive CTE needs a base case and a recursive case, conventionally joined by UNION ALL — plain UNION also works and removes duplicates
• A view cannot reference itself, but a view over a recursive CTE is perfectly valid
• Views persist in the database; CTEs exist only during query execution — name a fact with a view, name a question with a CTE

Pro Tip: Use CTEs to replace deeply nested subqueries. They make your SQL read from top to bottom instead of inside-out, which is much easier for humans to follow.

Next Steps

With views and CTEs, your queries are organized and maintainable. But what happens when multiple operations need to succeed or fail as a unit — like transferring money between two accounts? The next lesson covers transactions: the mechanism that lets you group several writes and then decide, once, whether to keep them or throw them all away. You will learn BEGIN, COMMIT, ROLLBACK and the ACID properties — including the part most people get wrong, which is how much of that decision the database makes for you and how much your own script has to make.