Examples of Complex Queries with Multiple CTEs

In this lecture, we're gonna do some real query magic! We'll create a few examples using multiple CTEs to show how they can be integrated with each other to build complex, multi-step queries. These examples are super useful in real life, especially for tough analytical tasks.

Example 1: Student Performance Analysis

Imagine we have a university database with three tables:

Table students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

Table grades:

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

Table courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

Task: get a list of students whose average grade is above 85, showing their average grade and the names of the courses they're taking.

Query:

WITH high_achievers AS (
    -- Find students with a high average grade
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- Find courses each student is enrolled in
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- Combine the results
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

Explanation:

1. In the first CTE (high_achievers), we calculate the average grade for each student and pick those whose grades are above 85.
2. In the second CTE (student_courses), we match students with their courses.
3. In the main query, we join data from both CTEs to get a list of students, their average grade, and the courses they're taking.

Example 2: Sales Report for an Online Store

Let's say we're working with an online store and we have these tables:

Table orders:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Table customers:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

Table order_items:

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Task: build a report showing for each customer:

• Total number of orders.
• Total amount of all orders in the last month.
• A list of all unique products they've bought.

Query:

WITH recent_orders AS (
    -- Select orders from the last month
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- Count total orders and sum for each customer
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- Select unique products each customer bought
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Combine the results
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

Explanation:

1. In the first CTE (recent_orders), we select orders from the last month.
2. In the second CTE (customer_summary), we calculate the total number of orders and the total amount for each customer.
3. In the third CTE (customer_products), we get the unique products each customer bought.
4. In the final query, we combine the data and use ARRAY_AGG() to make a list of unique products.

Example 3: Employee Hierarchy Analysis

We have an employees table:

Table employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

Task: build an employee hierarchy starting from the CEO. Show the level of each employee in the hierarchy.

Query:

WITH RECURSIVE employee_hierarchy AS (
    -- Start with employees who don't have a manager (the CEO)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Add all subordinates of the current level
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Show the hierarchy
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. In the recursive query, we start with the CEO (employees without a manager).
2. At each step, we add subordinates of the current level, increasing the level by one.
3. In the main query, we select the whole hierarchy, sorted by level and employee ID.

Handy Tips and Common Mistakes

• Too many CTEs: don't use them where a subquery would do. CTEs can sometimes slow things down because of data materialization.
• CTE names: give your CTEs clear and short names so your queries stay readable.
• Execution order: remember that CTEs run strictly in the order you declare them.
• Grouping data: use GROUP BY only when you really need it to avoid extra operations.

All these examples show how you can use CTEs to break down complex tasks into steps, making your queries easier to read and maintain. Now you're armed with the tools to tackle tough analytical problems with PostgreSQL!