Complex Querying with SQL

Beyond Basic Queries

You've already mastered fetching data with SELECT and combining tables with JOIN. Now, it's time to tackle the kind of complex questions that define professional data analysis. This involves structuring queries for clarity and performing sophisticated calculations directly within the database.

We'll start by organizing complex logic with tools that make your SQL more readable and modular, then move on to a powerful class of functions that analyze data in ways GROUP BY simply can't.

Untangling Queries with CTEs

As your queries grow, they can become a tangled mess of nested subqueries. It’s like trying to read a book with footnotes inside of other footnotes. A Common Table Expression (CTE) lets you clean this up by creating a temporary, named result set that you can reference within your main query.

Think of a CTE as giving a name to a specific step in your analysis. It makes the logic flow clearly from one step to the next, which is invaluable for debugging and for others trying to understand your work. CTEs are defined using a WITH clause at the beginning of your query.

WITH RegionalSales AS (
  -- This is our CTE definition
  SELECT
    region,
    SUM(sale_amount) AS total_sales
  FROM sales
  GROUP BY region
)
-- Now we use the CTE in our main query
SELECT
  region,
  total_sales
FROM RegionalSales
WHERE total_sales > 100000;

The query first defines RegionalSales as an aggregation of sales by region. Then, the main query, which comes after the CTE definition, simply selects from RegionalSales as if it were a real table. You can even chain multiple CTEs together to build a sequential, easy-to-follow data pipeline right inside your query.

A New Window on Your Data

Standard aggregate functions like SUM() and AVG() are powerful, but they require a GROUP BY clause, which collapses your rows. What if you want to calculate a running total or rank employees by salary within their department, all while keeping the original rows intact? For that, you need window functionss.

A window function performs a calculation across a set of rows that are related to the current row. This set of rows is called the "window frame," and it's defined by the OVER() clause. This clause is the key to unlocking their power.

The OVER() clause can contain two key parts:

PARTITION BY: This divides the rows into groups, or partitions. The window function is then applied independently to each partition. In the example above, we'd partition by department.
ORDER BY: This sorts the rows within each partition. This is essential for functions that depend on order, like RANK() or calculating a running total.

Let's see it in action. To rank employees by salary within each department, you would write:

SELECT 
  employee_name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

This query returns every employee, but with an extra column showing their rank. Two other useful ranking functions are ROW_NUMBER(), which assigns a unique number to each row, and DENSE_RANK(), which doesn't skip numbers after a tie.

Window functions aren't just for ranking. You can use them with aggregate functions to compute things like moving averages. For instance, to calculate a 7-day moving average of sales:

SELECT
  sale_date,
  daily_total,
  AVG(daily_total) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS 7_day_moving_avg
FROM daily_sales;

The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW clause defines the specific "window" of rows to include in the average for each day: that day and the six before it.

Handling Messy Data

Real-world data is rarely clean. You'll often encounter duplicates, missing values, and other inconsistencies that can skew your analysis. Advanced JOIN patterns combined with CTEs and window functions are your primary tools for cleaning this data at the source.

Imagine you have a customers table and an orders table, but the customers table has duplicate entries for some people. A simple JOIN would multiply your order data, leading to incorrect sums. The solution is to deduplicate the data before joining.

WITH DedupedCustomers AS (
  SELECT * FROM (
    SELECT 
      *,
      ROW_NUMBER() OVER(PARTITION BY email ORDER BY created_at DESC) as rn
    FROM customers
  ) tmp
  WHERE rn = 1
)
SELECT
  c.customer_id,
  o.order_amount
FROM orders o
JOIN DedupedCustomers c ON o.customer_id = c.customer_id;

In this pattern, the CTE first uses ROW_NUMBER() to assign a number to each customer record, partitioned by email and ordered by creation date. By selecting only rows where rn = 1, we are effectively keeping only the most recent record for each email address. The final JOIN is then performed against this clean, deduplicated set.

Handling NULL values is another common challenge. A standard INNER JOIN will drop rows where the join key is NULL in either table. If you need to include these, a LEFT JOIN is the obvious choice. But you can also explicitly handle NULLs using the COALESCE function to provide a default value if a key is missing.

Optimizing Your Queries

Writing a query that works is one thing. Writing a query that runs efficiently on millions of rows is another. When a query is slow, your first step should be to ask the database how it's running the query. Most SQL databases provide a command for this, often EXPLAIN or EXPLAIN PLAN.

This command doesn't run the query. Instead, it returns the query execution plan—the step-by-step recipe the database will follow to get your data. It tells you which tables it will access, in what order, and what method it will use to join them (e.g., a Nested Loop, Hash Join, or Merge Join).

EXPLAIN SELECT * 
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.signup_date > '2023-01-01';

Reading an execution plan is a skill, but you're typically looking for red flags like a "Full Table Scan." This means the database had to read every single row in a table to find what it needed. Often, this indicates a missing index on a column used in a WHERE clause or JOIN condition.

By adding an index to a column, you give the database a shortcut, like the index in the back of a book. It can find the rows it needs much faster, dramatically improving query performance.

These advanced techniques—CTEs for clarity, window functions for deep analysis, and execution plans for performance—are the building blocks for creating robust, scalable, and insightful data workflows directly in SQL.

Let's check your understanding of these advanced querying techniques.

Quiz Questions 1/5

What is the primary purpose of a Common Table Expression (CTE) in SQL?

Quiz Questions 2/5

How does a window function fundamentally differ from a standard aggregate function (like SUM()) used with a GROUP BY clause?

Mastering these patterns will ensure the data you pass to downstream tools and applications is clean, accurate, and efficiently retrieved.

Intermediate Data Analytics and Professional Workflows