Advanced SQL Extractions

Taming Complexity with CTEs

As your questions get more complex, your queries can turn into a tangled mess of nested subqueries. They work, but they are difficult to read, debug, and maintain. A cleaner approach is to use a Common Table Expression, or CTE.

A Common Table Expression (CTE) is a feature supported by most modern SQL engines that lets you break your query into smaller, readable chunks using the WITH keyword.

Think of a CTE as a temporary, named result set that exists only for the duration of a single query. You define it once at the beginning using a WITH clause, give it a name, and then you can refer to it in your main query as if it were a regular table. This makes your logic modular and far easier to follow.

Imagine you want to find all employees who earn more than the average salary for their department. Using a subquery, it might look like this:

-- Using a subquery (more nested)
SELECT
  employee_name,
  salary,
  department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e1.department_id = e2.department_id
);

Now, let's refactor that logic using a CTE to first calculate the average salary for each department. Notice how it separates the logic into clear, sequential steps.

-- Using a CTE (more readable)
WITH DepartmentAverages AS (
  SELECT
    department_id,
    AVG(salary) as avg_dept_salary
  FROM employees
  GROUP BY department_id
)
SELECT
  e.employee_name,
  e.salary
FROM employees e
JOIN DepartmentAverages da
  ON e.department_id = da.department_id
WHERE e.salary > da.avg_dept_salary;

The CTE version is longer, but each part does one job. The WITH block calculates the averages. The final SELECT statement uses those averages for filtering. If your logic gets even more complex, you can define multiple CTEs, with later ones even referring to earlier ones, creating a clean, step-by-step data pipeline within a single query.

Peeking Across Rows

Standard aggregate functions like SUM() or AVG() are great, but they collapse your rows using GROUP BY. What if you want to perform calculations across multiple rows while keeping the individual rows intact? For this, you need window functions.

A window function calculates a value for each row based on a "window" of related rows defined by the OVER() clause. Let's look at ranking functions, a common use case. Say we want to rank employees by salary within each department.

SELECT
  employee_name,
  department,
  salary,
  ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as row_num,
  RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

This query returns each employee record, plus three new columns. The PARTITION BY department clause creates a separate window for each department. Within each window, the rows are ordered by salary.

ROW_NUMBER() assigns a unique, sequential number to each row. No two rows will have the same number.
RANK() assigns a rank based on the ORDER BY value. It leaves gaps after a tie. For example, if two employees tie for 2nd place, the next rank will be 4th.
DENSE_RANK() also handles ties, but it doesn't leave gaps. If two employees tie for 2nd, the next rank is 3rd.

Employee	Department	Salary	row_num	rank	dense_rank
Alice	Sales	90000	1	1	1
Bob	Sales	85000	2	2	2
Carol	Sales	85000	3	2	2
David	Sales	70000	4	4	3
Eve	Eng	95000	1	1	1
Frank	Eng	92000	2	2	2

Time Travel and Trends

Window functions go beyond ranking. They can also "peek" at neighboring rows, which is perfect for analyzing trends. The LAG() and LEAD() functions are essential tools for period-over-period analysis.

LAG(column, offset) fetches the value of column from a previous row, specified by the offset. LEAD() does the same but for a subsequent row. This makes calculating things like month-over-month sales growth trivial.

WITH MonthlySales AS (
  SELECT
    DATE_TRUNC('month', order_date) as sales_month,
    SUM(order_total) as total_sales
  FROM orders
  GROUP BY 1
)
SELECT
  sales_month,
  total_sales,
  LAG(total_sales, 1) OVER (ORDER BY sales_month) as previous_month_sales
FROM MonthlySales;

From this result, you can easily calculate the growth percentage in your final SELECT statement. Another powerful technique is calculating a rolling average to smooth out daily fluctuations and see the underlying trend.

-- Calculate a 7-day rolling average of daily users
SELECT
  activity_date,
  daily_active_users,
  AVG(daily_active_users) OVER (
    ORDER BY activity_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as seven_day_rolling_avg
FROM daily_user_stats;

The ROWS BETWEEN clause defines the exact window frame for the aggregation. In this case, it's the current row plus the six rows that came before it.

Advanced Ways to Join

You're likely familiar with INNER and LEFT joins. But some scenarios require more specialized tools. A self-join is a technique where you join a table to itself. It’s not a special command, but a regular join used for a specific purpose: comparing rows within the same table.

The classic example is an employees table that includes a manager_id column. To get each employee's name next to their manager's name, you can join the table to itself, aliasing them to treat them as two separate tables.

SELECT
  e.employee_name AS employee,
  m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

A CROSS JOIN is simpler but more dangerous. It creates a Cartesian product, matching every row from the first table with every row from the second. If you have a table of 10 products and a table of 5 store locations, a CROSS JOIN will produce 50 rows, listing every possible combination. This can be useful for generating all possible pairs for analysis, but use it with caution, as it can create massive result sets unexpectedly.

Finally, for filtering, EXISTS provides a highly efficient way to check for the existence of related rows in another table without actually joining the data. It's often faster than using IN or a JOIN when you only need to know if a match exists, not what the matching data is.

-- Find all customers who have placed at least one order
SELECT
  c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

The subquery inside EXISTS returns TRUE if it finds at least one matching row, which is all the outer query needs to include the customer. NOT EXISTS works the same way to find records that have no match.

Professional Data Analysis Workflow