Advanced SQL Queries

Beyond Basic Queries

You've already mastered the fundamentals of SQL, pulling and filtering data with SELECT, WHERE, and GROUP BY. But some questions are too complex for a single, straightforward query. To answer them, you need to structure your queries in more sophisticated ways. We'll explore three powerful techniques for leveling up your SQL skills: subqueries, Common Table Expressions (CTEs), and window functions.

Subqueries: Queries Within Queries

A subquery, also known as an inner query or nested query, is a query that is nested inside another SQL statement. It's a powerful tool for performing sequential steps within a single command.

The database executes the subquery first, and its result is then used by the outer query. Let's say we want to find all employees who earn more than the company's average salary. You can't do this in one simple step because you first need to calculate the average salary. A subquery makes this easy.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

Here, (SELECT AVG(salary) FROM employees) is the subquery. It runs first, calculates the average salary (let's say it's $60,000), and returns that single value. The outer query then becomes SELECT first_name, last_name, salary FROM employees WHERE salary > 60000;, which is simple for the database to execute.

Subqueries can also return multiple rows. For example, you could use a subquery with the IN operator to find all managers who also appear in a separate table of top performers.

While subqueries are useful, they can sometimes be less efficient than a JOIN. If you can rewrite a subquery using a JOIN, it's often a good idea to do so for better performance.

CTEs for Cleaner Code

When your queries become long and complex, with multiple subqueries, they can be difficult to read and debug. Common Table Expressions, or CTEs, help solve this problem. A CTE lets you define a temporary, named result set that you can reference within your main query. Think of it as creating a temporary, single-use view.

CTEs simplify complex queries by breaking them into smaller, reusable subparts.

You define a CTE using the WITH clause. Let's look at a scenario where we want to find the top 5 most profitable products sold in North America. This requires joining sales data, product information, and region data.

WITH NorthAmericaSales AS (
  SELECT
    s.product_id,
    s.quantity * p.price AS total_revenue
  FROM sales s
  JOIN stores st ON s.store_id = st.store_id
  JOIN products p ON s.product_id = p.product_id
  WHERE st.region = 'North America'
)

SELECT
  p.product_name,
  SUM(nas.total_revenue) AS total_na_revenue
FROM NorthAmericaSales nas
JOIN products p ON nas.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_na_revenue DESC
LIMIT 5;

The WITH NorthAmericaSales AS (...) block defines our CTE. It calculates the revenue for each sale in North America. The main query that follows is much simpler: it just queries the NorthAmericaSales CTE as if it were a real table, groups the data, and finds the top 5 products. This makes the logic clear and easy to follow.

Unlocking Insights with Window Functions

Window functions are one of the most powerful features in modern SQL. Like aggregate functions (SUM, AVG, COUNT), they perform a calculation across a set of rows. However, unlike aggregate functions, they do not collapse the rows. Instead, they return a value for each row based on a "window" of related rows.

A window is defined using the OVER() clause. Let's see how it works. Imagine we want to show each employee's salary along with the average salary for their department.

SELECT 
  first_name,
  last_name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

The magic happens in AVG(salary) OVER (PARTITION BY department). The PARTITION BY department clause tells the database to create a window for each unique department. The AVG(salary) is then calculated for each window separately, and the result is returned alongside each employee's individual record. The original rows are preserved.

first_name	department	salary	avg_dept_salary
Alice	Sales	70000	65000
Bob	Sales	60000	65000
Charlie	Engineering	90000	95000
Diana	Engineering	100000	95000

Window functions are also perfect for ranking. For instance, ROW_NUMBER() can assign a unique rank to items within a partition. To find the top 3 highest-paid employees in each department:

WITH RankedSalaries AS (
  SELECT
    first_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
  FROM employees
)

SELECT
  first_name,
  department,
  salary
FROM RankedSalaries
WHERE rank <= 3;

Here, we combine a CTE with a window function. The ROW_NUMBER() function assigns a rank to each employee within their department, ordered by salary from highest to lowest. The outer query then simply filters for the rows where the rank is 3 or less.

Quiz Questions 1/6

What is the primary characteristic of a SQL subquery?

Quiz Questions 2/6

A key advantage of using a Common Table Expression (CTE) over a complex, nested subquery is improved __________.

Subqueries, CTEs, and window functions are essential tools for tackling complex data challenges. They allow you to break down problems into logical steps, write cleaner code, and perform sophisticated analysis that would be difficult or impossible with basic queries alone.

Mastering SQL Challenges