JOINs, Window Functions, CTEs, and Subqueries.

Introduction

SQL is one of the most important tools in data analytics. It allows analysts to retrieve, clean, transform, combine, and analyze data stored in relational databases.
When learning SQL, beginners usually start with SELECT, WHERE, ORDER BY, GROUP BY, and aggregate functions such as COUNT(), SUM(), and AVG(). These are essential, but more complex analytical questions require additional techniques.
Four particularly useful SQL concepts are JOINs, window functions, Common Table Expressions (CTEs), and subqueries.
In this article, I will explain how each technique works, when to use it, and how to apply it to practical business questions using PostgreSQL and a bookstore database that we used in class to learn.

1. Understanding SQL JOINs

A JOIN combines rows from two or more tables using a related column or condition. In a relational database, information is often stored in separate tables to reduce unnecessary duplication and maintain consistency.
Consider a bookstore database with three tables:

• sales.customers: Contains customer information.
• sales.books: Contains book information.
• sales.orders: Records customer orders, including the books purchased and quantities.

The orders table connects customers and books through their respective IDs. the customer, orders, and books.

Types of JOINs

INNER JOIN
Returns only rows with matching values in both tables.
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM sales.customers AS c
INNER JOIN sales.orders AS o
ON c.customer_id = o.customer_id;

This query returns customers who have placed orders. Customers without matching orders are excluded.

LEFT JOIN
Returns all rows from the left table and matching rows from the right table. If no match exists, the right-side columns contain NULL.
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM sales.customers AS c
LEFT JOIN sales.orders AS o
ON c.customer_id = o.customer_id;

This is useful when identifying customers who have never purchased a book.
RIGHT JOIN
Returns all rows from the right table and matching rows from the left table.
FULL OUTER JOIN
Returns matched and unmatched rows from both tables.
CROSS JOIN
Returns every possible combination of rows from two tables.
Why JOINs matter in data analytics
JOINs allow analysts to combine information that would otherwise be scattered across several tables.
For example, an analyst can combine customer details, book titles, quantities, and order dates to understand purchasing behaviour.
JOINs are particularly useful in reporting, customer analysis, financial reporting, and business intelligence projects.

2. Window Functions: Analysing Rows Without Losing Detail

Window functions perform calculations across related rows while retaining individual rows in the query result.
This is an important distinction between window functions and GROUP BY. Grouping generally reduces records to one row per group, whereas window functions allow you to calculate values while preserving the underlying records.
In PostgreSQL we will use window functions through the OVER() clause.
Common window functions
• ROW_NUMBER(): Assigns a sequential number to each row.
• RANK(): Assigns ranks, leaving gaps after ties.
• DENSE_RANK(): Assigns ranks without gaps after ties.
• LAG(): Retrieves a value from a previous row.
• LEAD(): Retrieves a value from a following row.
• SUM() OVER(): Calculates totals across a window.
• AVG() OVER(): Calculates averages across a window.

Example 1: Ranking books by price
Suppose the bookstore wants to identify its most expensive books.
SELECT
title,
price,
RANK() OVER (
ORDER BY price DESC
) AS price_rank
FROM sales.books;
The query ranks books from the highest price to the lowest. Books with equal prices receive the same rank.

Example 2: Numbering customer orders
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS order_number
FROM sales.orders;
The PARTITION BY clause creates a separate sequence for each customer. The ORDER BY clause determines the order in which their transactions are numbered.
This can help analysts identify first purchases, repeat purchases, and customer ordering patterns.
Example 3: Calculating 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 sales.orders;
This query calculates the cumulative number of book copies sold as orders progress chronologically.
Window functions are especially useful for ranking, trend analysis, running totals, comparisons, and customer behaviour analysis.
3. Common Table Expressions (CTEs)
A Common Table Expression, commonly called a CTE, is a named result set defined using the WITH keyword.
CTEs help break complicated queries into smaller, more understandable steps. In PostgreSQL, a regular CTE is available only within the SQL statement in which it is defined.
Example: Finding total copies sold per book

WITH book_sales AS (
SELECT
book_id,
SUM(quantity) AS copies_sold
FROM sales.orders
GROUP BY book_id
)
SELECT
book_id,
copies_sold
FROM book_sales
ORDER BY copies_sold DESC;

Here is what happens:
1.The CTE calculates total copies sold for each book.
2.The result is given the name book_sales.
3.The main query retrieves the calculated results.
4.The results are sorted from the most copies sold to the fewest.
The benefit is that the calculation and final reporting steps are clearly separated.

Using multiple CTEs
CTEs can also be combined to handle more complex analytical tasks.
WITH customer_orders AS (
SELECT
customer_id,
COUNT(*) AS total_orders
FROM sales.orders
GROUP BY customer_id
),
customer_spending AS (
SELECT
customer_id,
SUM(o.quantity * b.price) AS total_spent
FROM sales.orders AS o
JOIN sales.books AS b
ON o.book_id = b.book_id
GROUP BY customer_id
)
SELECT
co.customer_id,
co.total_orders,
cs.total_spent
FROM customer_orders AS co
JOIN customer_spending AS cs
ON co.customer_id = cs.customer_id;
This query combines two calculations: order counts and estimated spending based on book prices.
Note that this is an estimate of spending using current book prices, not necessarily the actual historical amount paid.
CTEs are useful when queries contain several calculation steps, when logic needs to be easier to maintain, or when analysts want to organize complex reports.

4. Subqueries: Using One Query Inside Another

A subquery is a query nested inside another SQL statement. Its result can be used as a value, a filter, or a temporary result set.
Subqueries are useful when the answer to one question is needed to answer another.
Example 1: Books priced above average
SELECT
title,
price
FROM sales.books
WHERE price > (
SELECT AVG(price)
FROM sales.books
);
The inner query calculates the average price of all books. The outer query returns books priced above that average.
This is an example of a scalar subquery because the inner query returns one value.
Example 2: Finding customers who have placed orders
SELECT
c.customer_id,
c.first_name,
c.last_name
FROM sales.customers AS c
WHERE EXISTS (
SELECT 1
FROM sales.orders AS o
WHERE o.customer_id = c.customer_id
);
The EXISTS operator checks whether a matching order exists for each customer.
To find customers who have never placed an order, use NOT EXISTS instead.
Example 3: Using a subquery in the FROM clause
SELECT *
FROM (
SELECT
book_id,
SUM(quantity) AS copies_sold
FROM sales.orders
GROUP BY book_id
) AS book_totals
WHERE copies_sold > 2;
The inner query calculates copies sold for each book. The outer query filters the calculated results to show books with more than two copies sold.
A subquery in the FROM clause must have an alias in PostgreSQL, which is why the example uses AS book_totals.
Correlated and non-correlated subqueries
A non-correlated subquery can run independently of the outer query.
A correlated subquery references a column from the outer query. For example, the customer ID in the EXISTS example connects the inner query to the current customer.
Subqueries can appear in WHERE, FROM, and SELECT clauses, among other contexts.

5. Comparing the Four Techniques

Although these techniques can be combined, each serves a different purpose.
Technique Main purpose Example use case
JOIN Combines related tables Display customer names with their orders
Window function Calculates across rows while preserving detail Rank books by price
CTE Organizes a query into named steps Calculate and report total copies sold
Subquery Uses one query's result within another Find books priced above average
The choice depends on the question being asked, the structure of the data, and which approach makes the logic easiest to understand.
A CTE and a subquery can sometimes solve the same problem. Neither is automatically faster in every situation. Query performance depends on the database, query structure, indexes, and execution plan.

1. Combining the Techniques in One Query The real value of SQL becomes clearer when these techniques are used together. Suppose a bookstore wants to identify its top customers based on the total number of book copies purchased, while also showing the customer's name and ranking.

WITH customer_sales AS (
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(o.quantity) AS total_copies
FROM sales.customers AS c
JOIN sales.orders AS o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name
),
ranked_customers AS (
SELECT
customer_id,
first_name,
last_name,
total_copies,
DENSE_RANK() OVER (
ORDER BY total_copies DESC
) AS sales_rank
FROM customer_sales
)
SELECT
customer_id,
CONCAT(first_name, ' ', last_name) AS full_name,
total_copies,
sales_rank
FROM ranked_customers
ORDER BY sales_rank, customer_id;

This query uses:
• A JOIN to connect customers and orders.
• SUM() and GROUP BY to calculate copies purchased per customer.
• CTEs to organize the calculations into two steps.
• A window function to rank customers by total copies purchased.
• CONCAT() to combine customer names.
The result is a ranked customer report that could support sales analysis and customer engagement decisions.

7. Common Mistakes to Avoid

1. Confusing COUNT and SUM COUNT(order_id) counts orders. SUM(quantity) adds up copies purchased. A customer can place one order containing several copies.
2. Using the wrong JOIN An INNER JOIN excludes unmatched records. A LEFT JOIN retains all rows from the left table. Always decide whether unmatched records should appear in the result.
3. Forgetting GROUP BY requirements When selecting non-aggregated columns alongside aggregate functions, make sure your grouping is appropriate.
4. Confusing window functions with grouped results A window function generally retains individual rows, while GROUP BY aggregates rows into groups.
5. Forgetting aliases Derived tables in the FROM clause require aliases in PostgreSQL. Clear aliases also make complex queries easier to read.
6. Assuming CTEs always improve performance CTEs improve readability, but they are not automatically faster than subqueries. Check execution plans when performance matters.

Conclusion

JOINs, window functions, CTEs, and subqueries are essential SQL techniques for moving beyond basic data retrieval.
JOINs help combine related information. Window functions support rankings, running totals, and comparisons. CTEs make complicated queries easier to organize, while subqueries allow one calculation to inform another.
For anyone learning data analytics, the best approach is to practise these concepts using real questions rather than memorizing syntax alone.
Start with a simple query, understand the result it produces, and gradually combine techniques as the analytical question becomes more complex.
SQL is not just about retrieving data. It is about asking the right questions and turning database records into useful information for decision-making.