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.

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.

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;

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;

CTEs are defined with WITH cte_name AS (...). You can chain multiple CTEs separated by commas. Each CTE can reference the ones defined before it.

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:

Base case: the initial SELECT that doesn't reference the CTE
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.

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.

Views vs CTEs

Both simplify complex queries, but they serve different purposes:

Feature	Views	CTEs
Persistence	Stored in database	Exists only during query
Reusability	Any query can reference them	Only within the same query
Recursion	Not supported	Supported
Use case	Shared logic across queries	Single complex query

Practice Exercises

Try these on your own:

Create a view that shows each department's headcount and average salary
Write a CTE that finds the highest-paid employee in each department
Use a recursive CTE to generate numbers from 1 to 20
Create a view for product sales and then query it to find the best-selling category
Build an org chart recursive CTE and find all employees under a specific manager

Key Takeaways

Views are saved queries that act like virtual tables — use them for frequently needed logic
CTEs use WITH ... AS (...) to create temporary result sets within a single query
You can chain multiple CTEs, each building on the previous one
Recursive CTEs handle hierarchical data and sequence generation
A recursive CTE needs a base case and a recursive case joined by UNION ALL
Views persist in the database; CTEs exist only during query execution

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 databases use to guarantee data consistency. You will learn BEGIN, COMMIT, ROLLBACK, and the ACID properties that make databases reliable even when things go wrong.