Advanced SQL guide

Window functions, CTEs, and query optimization

You know basic SQL. Now level up with window functions, common table expressions, subqueries, and query optimization techniques that separate junior from senior data analysts.

Beyond SELECT — what advanced SQL unlocks

Basic SQL (SELECT, WHERE, JOIN, GROUP BY) gets you to junior analyst level. Advanced SQL — window functions, CTEs, subqueries, and performance optimization — gets you to senior analyst and analytics engineer level. These techniques let you write queries that would otherwise require multiple queries, external tools, or engineering support.

Common Table Expressions (CTEs)

A CTE is a named temporary result set that you can reference within the same query. It makes complex queries readable by breaking them into named steps.

Syntax

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', created_at) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month
FROM monthly_revenue;

When to use CTEs

When you need to reference the same subquery multiple times, or when breaking a complex query into steps improves readability.

Window functions

Window functions perform calculations across rows related to the current row, without collapsing the result set. Unlike GROUP BY, they keep every row.

ROW_NUMBER()

Sequential row number within a partition.

RANK() / DENSE_RANK()

Rank within a partition (with ties).

LAG() / LEAD()

Access previous / next row value.

SUM() OVER()

Running total.

PARTITION BY

Perform window calculations within groups.

Example — rank customers by total spend

SELECT customer_id,
       total_spend,
       RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
FROM customer_summary;

Subqueries

A subquery is a query nested inside another query. Use them when you need to filter based on an aggregated result.

Example — users who spent above average

SELECT user_id, total_spend
FROM user_summary
WHERE total_spend > (SELECT AVG(total_spend) FROM user_summary);

When CTEs beat subqueries

When the subquery is referenced more than once, or when readability matters.

Query optimization basics

Fast queries matter at scale. These five habits keep your queries performant as data volumes grow.

Filter early

Apply WHERE clauses before JOINs where possible.

Avoid SELECT *

Specify columns explicitly — reduces data transfer.

Use EXPLAIN / EXPLAIN ANALYZE

Shows the query execution plan — where time is being spent.

Indexes

Columns used in WHERE, JOIN, and ORDER BY benefit from indexes.

Limit large scans

Use LIMIT during development, partition filters in production.

Start from the beginning

New to SQL?

Window functions and CTEs build on the fundamentals. If SELECT, WHERE, and JOIN are still fuzzy, start with the SQL basics guide first.

Learn SQL from scratch