Mastering SQL: Window Functions, JOINs, CTEs, and Subqueries

Introduction

SQL is an essential tool for anyone learning data analysis. It allows us to retrieve, filter, organize, and analyze information stored in relational databases.

When starting out, we usually learn commands such as SELECT, WHERE, ORDER BY, and GROUP BY. These are important foundations, but more complex analytical questions require additional techniques.

For example, imagine working with a bookstore database. How would you identify customers who have never placed an order? How could you rank books by price, calculate running sales totals, or find customers who purchased more copies than the average?

This is where JOINs, window functions, Common Table Expressions (CTEs), and subqueries become useful.

In this article, I will explain how these four SQL techniques work, provide practical PostgreSQL examples, and demonstrate how they can be combined to solve analytical problems.

The examples assume a bookstore database with sales.customers, sales.books, and sales.orders tables. Adjust table and column names to match your actual class database.

1. Understanding SQL JOINs

A join combines related rows from two or more tables using a matching condition.

In a relational database, information is often separated into different tables to reduce duplication and maintain consistency.

For example, a bookstore might have:

1. sales.customers — customer details.

2. sales.books — book titles and prices.

3. sales.orders — customer orders and quantities purchased.

Suppose we want to retrieve customers alongside their order records.

SELECT c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;

The ON clause connects customers to their orders through customer_id. The aliases c and o make the query easier to read.

Types of JOINs

INNER JOIN

An inner join returns only rows that have matching values in both tables.

It is useful when we want to analyze customers who have placed orders.

LEFT JOIN

A left join returns every row from the left table and any matching rows from the right table. If there is no match, the right-side columns contain NULL.

For example, we can find customers who have never placed an order:

SELECT c.customer_id, c.first_name, c.last_name
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

The where condition filters the results to customers without matching orders.

RIGHT JOIN

A right join returns every row from the right table and matching rows from the left table.

SELECT c.customer_id, c.first_name, o.order_id
FROM customers AS c
RIGHT JOIN orders AS o
ON c.customer_id = o.customer_id;

This preserves every order, even if no matching customer record exists.

FULL OUTER JOIN

A full outer join returns matching records and unmatched records from both tables.

SELECT c.customer_id, c.first_name, o.order_id
FROM customers AS c
FULL OUTER JOIN orders AS o
ON c.customer_id = o.customer_id;

This can help when comparing datasets and identifying records that exist on only one side.

CROSS JOIN

A cross join returns every possible combination of rows from two tables.

SELECT c.customer_id, b.book_id
FROM customers AS c
CROSS JOIN books AS b;

If there are 10 customers and 20 books, the query returns 200 combinations.

SELF JOIN

A self join joins a table to itself. It is useful when records in a table have relationships with other records in that same table.

For example, if the customer table contains a referred_by_customer_id column, we can identify customers and the people who referred them.

SELECT c.first_name AS customer_name, r.first_name AS referrer_name
FROM sales AS c
LEFT JOIN customers AS r
ON c.referred_by_customer_id = r.customer_id;

This example assumes the referral column exists in the database.

Why JOINs matter

Joins allow analysts to combine information that would otherwise be spread across multiple tables. They are useful in reporting, customer analysis, financial analysis, and dashboard development.

Before choosing a JOIN, ask yourself which records must appear in the final result, including those without matches?

The answer helps determine the appropriate join type.

2. Window Functions - Analyzing Rows Without Losing Detail

Window functions perform calculations across related rows while retaining individual rows in the result.

This is an important difference between window functions and GROUP BY.

For example, GROUP BY can calculate one total per customer, while a window function can display every order alongside a calculation for that customer's orders.

Window functions in PostgreSQL use the OVER() clause.

Understanding OVER(), PARTITION BY, and ORDER BY

• OVER() defines the window used for the calculation.

• PARTITION BY divides rows into groups.

• ORDER BY determines the order in which rows are processed.

Consider the following example:

SELECT customer_id, order_id, order_date,ROW_NUMBER() 
OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS order_number
FROM orders;

This assigns a sequential number to each customer's orders. The numbering restarts for every customer.

Ranking functions

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row in a window.

SELECT title, price, ROW_NUMBER() 
OVER (ORDER BY price DESC, book_id) AS row_number
FROM books;

The query numbers books from highest price to lowest. The book_id provides a tie-breaker when prices are equal.

RANK()

RANK() assigns the same rank to tied values but leaves gaps after ties.

SELECT title, price, RANK() 
OVER (ORDER BY price DESC) AS price_rank
FROM books;

If two books share first place, the next rank is third.

DENSE_RANK()

DENSE_RANK() also gives tied values the same rank, but it does not leave gaps.

If two books share first place, the next rank is second.

SELECT title, price, DENSE_RANK() 
OVER (ORDER BY price DESC) AS price_rank
FROM books;

NTILE()

NTILE() divides ordered rows into a specified number of approximately equal groups.

SELECT title, price, NTILE(4) 
OVER (ORDER BY price) AS price_group
FROM books;

This divides the books into four price groups, with group 1 containing the lowest prices and group 4 containing the highest prices.

Navigation functions

LAG()

LAG() retrieves a value from a preceding row.

SELECT customer_id, order_id, order_date, quantity, LAG(quantity)
OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS previous_quantity
FROM orders;

This allows us to compare an order's quantity with the quantity recorded in the customer's previous order.

LEAD()

LEAD() retrieves a value from a following row.

SELECT customer_id, order_id, quantity, LEAD(quantity)
OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS next_quantity
FROM orders;

This can help identify changes between consecutive orders.

FIRST_VALUE()

FIRST_VALUE() returns the value from the first row in the window frame.

SELECT title, price, FIRST_VALUE(title) 
OVER (ORDER BY price DESC, book_id) AS highest_priced_book
FROM books;

The query displays the title of the highest-priced book alongside every row.

LAST_VALUE()

LAST_VALUE() is a window function that returns the value from the final row within a specified window frame.

The critical "trap" with LAST_VALUE() is that if you use ORDER BY, the default frame stops at the current row. To get the actual last value of the entire set, you must explicitly force the frame to look ahead to UNBOUNDED FOLLOWING.

SELECT title, price, LAST_VALUE(title) 
OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS most_expensive
FROM books;

Because the books are ordered from lowest to highest price, the last value represents the highest-priced book.

Aggregate window functions

Aggregate functions can also operate as window functions.

SUM() — Running totals

SELECT order_id, order_date, quantity, SUM(quantity) 
OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_copies_sold
FROM orders;

This calculates a cumulative total of the quantities recorded in the orders table.

AVG() — Averages

SELECT customer_id, order_id, quantity, AVG(quantity) 
OVER (PARTITION BY customer_id) AS average_quantity
FROM orders;

Every order remains visible, alongside the average quantity for that customer.

COUNT() — Counting records

SELECT customer_id, order_id, COUNT(*) 
OVER (PARTITION BY customer_id) AS customer_order_count
FROM orders;

This displays the number of order records for each customer alongside each order.

MIN() — Minimum values

SELECT title, price, MIN(price) 
OVER () AS lowest_book_price
FROM books;

The lowest book price appears alongside every book.

Window functions are particularly useful for ranking, running totals, comparisons, and trend analysis.

3. Common Table Expressions (CTEs)

A Common Table Expression, or CTE, is a named result set created using the WITH keyword.

CTEs help divide complicated queries into smaller, easier-to-understand steps.

Example - Calculating copies sold per book

Suppose we want to identify the books with the highest total quantities sold.

WITH book_sales AS (
    SELECT book_id, SUM(quantity) AS copies_sold
    FROM orders
    GROUP BY book_id
)
SELECT b.title, bs.copies_sold
FROM book_sales AS bs
JOIN books AS b
ON bs.book_id = b.book_id
ORDER BY bs.copies_sold DESC;

The query works in stages:

1. The CTE calculates total copies sold per book.

2. The main query joins those totals to the books table.

3. The final result displays each title and its sales total.

4. The results are sorted from highest to lowest quantity.

The benefit is that the calculation and reporting steps are clearly separated.

Using multiple CTEs

Several CTEs can be combined when an analysis requires multiple stages.

WITH customer_summary AS (
    SELECT customer_id, COUNT(*) AS total_order_records, SUM(quantity) AS total_copies
    FROM orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT customer_id, total_order_records, total_copies, DENSE_RANK()
    OVER (ORDER BY total_copies DESC) AS sales_rank
    FROM customer_summary
)
SELECT customer_id, total_order_records, total_copies, sales_rank
FROM ranked_customers
ORDER BY sales_rank, customer_id;

The first CTE calculates customer totals. The second assigns rankings based on total copies purchased. The final query presents the results.

Recursive CTEs

A recursive CTE references its own result and repeatedly builds on it. It is useful for hierarchical data, such as organizational structures, or for generating sequences.

Here is a simple example that generates numbers from 1 to 5.

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM number_sequence
    WHERE n < 5
)
SELECT n
FROM number_sequence;

The first query starts at 1. The recursive query adds 1 until the stopping condition is reached.

Why use CTEs?

CTEs are useful when:

• A query contains several logical steps.

• Intermediate calculations need clear names.

• The logic needs to be easier to read and maintain.

• Multiple transformations depend on earlier results.

A CTE improves organization, but it does not automatically make every query faster. Performance depends on the query structure, database version, and execution plan.

4. Subqueries - Using One Query Inside Another

A subquery is a SQL query placed inside another SQL statement.

Its result can be used as a value, a filter, or a derived table.

Scalar subqueries

A scalar subquery returns a single value.

For example, suppose we want to find books priced above the average price.

SELECT title, price
FROM books
WHERE price > (
    SELECT AVG(price)
    FROM books
);

The inner query calculates the average price. The outer query returns books whose prices are higher than that average.

Non-correlated subqueries

A non-correlated subquery can run independently of the outer query.

The average-price example is non-correlated because its inner query does not reference any column from the outer query.

Correlated subqueries

A correlated subquery references a column from the outer query.

Suppose the books table includes a category_id column and we want to find books priced above the average for their own category.

SELECT b.book_id, b.title, b.category_id, b.price
FROM books AS b
WHERE b.price > (
    SELECT AVG(b2.price)
    FROM books AS b2
    WHERE b2.category_id = b.category_id
);

The inner query calculates the average price for the current book's category. The outer query returns books priced above that average.

This example assumes category_id exists in the books table.

Using EXISTS

EXISTS checks whether a subquery returns at least one row.

For example, we can identify customers who have placed orders.

SELECT c.customer_id, c.first_name, c.last_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM sales.orders AS o
    WHERE o.customer_id = c.customer_id
);

The subquery checks for a matching order for each customer.

To find customers who have never ordered anything, use NOT EXISTS.

SELECT c.customer_id, c.first_name, c.last_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

Subqueries in the FROM clause

A subquery can produce a derived table for the outer query.

SELECT book_id, copies_sold
FROM (SELECT book_id, SUM(quantity) AS copies_sold
    FROM orders
    GROUP BY book_id
) AS book_totals
WHERE copies_sold > 5;

The inner query calculates the total copies sold per book. The outer query filters the result to books with more than five copies sold.

In PostgreSQL, a derived table in the FROM clause requires an alias, which is why the example uses AS book_totals.

Other useful subquery operators include:

• IN — checks whether a value matches any value in a returned set.

• ANY — compares a value with at least one value returned by a subquery.

  • ALL — compares a value with every value returned by a subquery.

Be careful with NULL values when using IN and NOT IN. In some cases, EXISTS and NOT EXISTS express the intended logic more clearly.

5. Combining the Four Techniques

The real value of these concepts becomes clearer when they are used together.

Suppose a bookstore wants a report showing each customer's name, number of order records, total copies purchased, and sales rank.

WITH customer_summary AS (
    SELECT c.customer_id, c.first_name, c.last_name, COUNT(o.order_id) AS total_orders, COALESCE(SUM(o.quantity), 0) AS total_copies
    FROM customers AS c
    LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
),
customer_ranking AS (
    SELECT customer_id, first_name, last_name, total_orders, total_copies, DENSE_RANK() 
    OVER (ORDER BY total_copies DESC) AS sales_rank
    FROM customer_summary
)
SELECT customer_id, CONCAT(first_name, ' ', last_name) AS customer_name, total_orders, total_copies, sales_rank
FROM customer_ranking
ORDER BY sales_rank, customer_id;

This query demonstrates several techniques:

1. JOIN - Connects customers with their orders.

2. Aggregate functions - Calculate the number of order records and total copies purchased.

3. CTEs - Separate the summary and ranking stages.

4. Window function - Ranks customers according to their total purchases.

5. COALESCE() - Displays zero instead of NULL for customers with no recorded purchases.

6. The LEFT JOIN ensures customers without orders remain in the report.

One detail to verify is the structure of the orders table. If one order can contain several books across multiple rows, COUNT(o.order_id) counts order records rather than unique orders. Use COUNT(DISTINCT o.order_id) if you need to count distinct order IDs.

6. Comparing the Four Techniques

1. Technique

2. Main purpose

3. Example use case

JOIN

• Combines related tables

• Display customer names alongside their orders

• Window function

• Calculates across rows while retaining detail

• Rank customers by total purchases

CTE

• Organizes query logic into named steps

• Calculate totals before ranking customers

Subquery

• Uses one query's result inside another

• Find books priced above average

A CTE and a subquery can sometimes solve the same problem. The best choice depends on the question, readability, and performance requirements.

Similarly, JOINs and subqueries can sometimes produce similar results, but the correct approach depends on the data and the logic required.

Understanding each technique's purpose is more important than memorizing syntax.

7. Common Mistakes to Avoid

• Confusing COUNT() and SUM() - COUNT() counts rows or non-NULL values, depending on its argument. SUM() adds numeric values. Counting order records is not the same as calculating total quantities purchased.

• Choosing the wrong JOIN - An INNER JOIN excludes unmatched records, while a LEFT JOIN preserves all rows from the left table.

• Forgetting GROUP BY requirements - When aggregate functions appear alongside ordinary columns, make sure the grouping is appropriate.

• Confusing window functions with grouped results - GROUP BY summarizes records into groups, while window functions generally retain individual rows.

• Ignoring the window frame - The default frame can affect functions such as LAST_VALUE(). Specify the frame when you need the final value across an entire partition.

• Assuming CTEs always improve performance - CTEs improve readability, but they are not automatically faster than subqueries. Query plans can help investigate performance.

Conclusion

JOINs, window functions, CTEs, and subqueries are important tools for moving beyond basic SQL.

JOINs combine related information. Window functions support rankings, comparisons, and running totals. CTEs organize complicated queries into manageable steps, while subqueries allow one calculation to inform another.

As I continue learning SQL for data analysis, practising these techniques with real datasets helps me understand how raw records can be transformed into meaningful reports.

SQL is not only about retrieving data. It is about asking useful questions and turning the answers into information that supports better decisions.

Thank you for reading!