Advanced SQL Mastery

Beyond the Basics with CTEs

You've likely written queries that spiral into a maze of nested subqueries. They work, but they're hard to read and even harder to debug. This is where Common Table Expressions, or CTEs, come in. 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 make your code modular. You can break down a complex problem into logical, readable steps. Each CTE handles one piece of the puzzle.

Let's say we want to find all employees who earn more than the average salary for their department. Without a CTE, you might use a subquery in the WHERE clause. With a CTE, the logic becomes much cleaner.

WITH DepartmentAvgSalary AS (
  SELECT
    department_id,
    AVG(salary) AS avg_dept_salary
  FROM employees
  GROUP BY department_id
)
SELECT
  e.employee_name,
  e.salary,
  d.avg_dept_salary
FROM employees e
JOIN DepartmentAvgSalary d
  ON e.department_id = d.department_id
WHERE e.salary > d.avg_dept_salary;

The WITH clause introduces our CTE, named DepartmentAvgSalary. We define it once and then join to it as if it were a regular table. This separation of concerns makes the final SELECT statement incredibly simple to understand. You can even chain multiple CTEs together, with each subsequent one building on the last.

Analytical Window Functions

Aggregate functions like SUM() and AVG() are powerful, but they collapse your rows. What if you want to calculate something over a group of rows while keeping the individual rows intact? That's the job of They perform calculations across a set of table rows that are somehow related to the current row.

Let's start with ranking. Imagine you need to rank employees by salary within each department.

RANK() assigns a rank based on the ORDER BY clause. If there's a tie, it assigns the same rank and then skips the next rank(s). So, two people tied for 2nd place would both get rank 2, and the next person would be rank 4.

DENSE_RANK() also assigns the same rank to ties, but it doesn't skip ranks. The person after the two-way tie for 2nd would be rank 3.

NTILE(n) distributes the rows into a specified number of ranked groups, n. For example, NTILE(4) would divide your employees into salary quartiles.

SELECT
  employee_name,
  department_name,
  salary,
  RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_rank,
  DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_dense_rank,
  NTILE(4) OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_quartile
FROM employees;

Window functions truly shine in time-series analysis. LAG() and LEAD() let you access data from a previous or subsequent row, respectively, without a costly self-join.

Want to calculate month-over-month sales growth? LAG() is your tool. You can pull the previous month's sales into the current month's row and perform the calculation directly.

WITH MonthlySales AS (
  SELECT
    DATE_TRUNC('month', order_date) AS sales_month,
    SUM(sale_amount) AS total_sales
  FROM sales
  GROUP BY 1
)
SELECT
  sales_month,
  total_sales,
  LAG(total_sales, 1) OVER (ORDER BY sales_month) AS previous_month_sales,
  (total_sales - LAG(total_sales, 1) OVER (ORDER BY sales_month)) / LAG(total_sales, 1) OVER (ORDER BY sales_month) AS mom_growth
FROM MonthlySales;

Navigating Hierarchies

Data often has a natural hierarchy, like an employee reporting to a manager, who reports to a director. Querying this kind of structure can be tricky. This is where become essential.

A recursive CTE is one that references itself. It consists of two parts: an anchor member that returns the base result set (e.g., the CEO), and a recursive member that repeatedly executes, joining back to the CTE itself to build out the hierarchy (e.g., finding direct reports).

Here’s how you could map out an entire organizational chart, starting from the CEO.

WITH RECURSIVE OrgChart AS (
  -- Anchor member: find the CEO
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member: find direct reports
  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    o.level + 1
  FROM employees e
  JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart;

The UNION ALL is crucial; it combines the anchor result with the results of each recursive step. The process stops when the recursive member returns no more rows.

Optimizing Your Queries

Writing a query that works is one thing. Writing one that runs efficiently on millions of rows is another. The key to performance tuning is understanding how the database plans to execute your query. The EXPLAIN (or EXPLAIN PLAN) command is your window into the database's brain.

Running EXPLAIN before your query shows you the —the sequence of steps the database will take. It reveals which tables are being scanned, what join methods are used (like a Nested Loop or Hash Join), and where it expects the highest costs.

EXPLAIN ANALYZE SELECT * FROM customers WHERE last_name = 'Smith';

If the query plan reveals a full table scan on the customers table, the most effective fix is often to add an index. An index is a special lookup table that the database search engine can use to speed up data retrieval. Think of it like the index at the back of a book. Instead of reading the whole book to find a topic, you look it up in the index and go straight to the right page.

CREATE INDEX idx_customers_last_name ON customers (last_name);

After creating the index, running the EXPLAIN command again should show a much more efficient plan, likely an 'Index Scan', which is dramatically faster. Proper indexing is arguably the single most important factor in query optimization for large datasets.

Quiz Questions 1/6

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

Quiz Questions 2/6

You need to rank employees by salary within each department. If two employees have the same salary, they should receive the same rank, and the next rank number should be skipped (e.g., 1, 2, 2, 4). Which window function should you use?

These techniques form the bedrock of modern data analysis and engineering. By moving beyond basic queries, you can tackle more complex problems with code that is not only powerful but also elegant and efficient.

Mastering Modern Data Analytics