Advanced SQL for Data Scientists – Window Functions, CTEs & More

SQL is the most universally required skill in data science — even more than Python or R in many job descriptions. But most data scientists only use basic SELECT, WHERE, GROUP BY queries. This guide covers the advanced SQL patterns that separate mid-level from senior data scientists: window functions, CTEs, subqueries, pivoting, and query optimization.

Window Functions – The Power Tool of Analytics SQL

Window functions compute a result for each row based on a related set of rows (the “window”), without collapsing the rows like GROUP BY does. They’re essential for ranking, running totals, moving averages, and lag/lead analysis.

-- Rank customers by revenue within each region
SELECT
    customer_id,
    region,
    revenue,
    RANK()        OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region,
    DENSE_RANK()  OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rank,
    ROW_NUMBER()  OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num
FROM orders;

Running Totals and Moving Averages

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue)  OVER (ORDER BY order_date
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                             ) AS cumulative_revenue,
    AVG(daily_revenue)  OVER (ORDER BY order_date
                              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
                             ) AS rolling_7day_avg
FROM daily_sales;

LAG and LEAD – Comparing Across Rows

SELECT
    month,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month_revenue,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change,
    ROUND(100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
          / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0), 2) AS mom_pct_change
FROM monthly_revenue;

Common Table Expressions (CTEs)

CTEs make complex queries readable by naming intermediate results. They’re like temporary views scoped to a single query.

WITH
-- Step 1: Calculate each customer's total orders
customer_orders AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
-- Step 2: Segment customers
customer_segments AS (
    SELECT
        customer_id,
        total_spent,
        CASE
            WHEN total_spent >= 10000 THEN 'platinum'
            WHEN total_spent >= 5000  THEN 'gold'
            WHEN total_spent >= 1000  THEN 'silver'
            ELSE 'bronze'
        END AS segment
    FROM customer_orders
)
-- Final: join segments back to customer info
SELECT c.name, c.email, cs.segment, cs.total_spent
FROM customers c
JOIN customer_segments cs USING (customer_id)
ORDER BY cs.total_spent DESC;

Recursive CTEs – Hierarchical Data

-- Traverse an employee reporting hierarchy
WITH RECURSIVE org_chart AS (
    -- Base case: top-level manager
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees whose manager is in the previous level
    SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT level, name FROM org_chart ORDER BY level, name;

Pivoting Data

-- Pivot: rows to columns (works in most SQL dialects)
SELECT
    product_category,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
FROM quarterly_sales
GROUP BY product_category;

Subqueries vs CTEs vs Joins

-- Correlated subquery (runs for each row — avoid on large tables)
SELECT customer_id, amount
FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE region = o.region);

-- Better: use window function
SELECT customer_id, amount,
       AVG(amount) OVER (PARTITION BY region) AS region_avg
FROM orders;

Query Optimization Essentials

Always use EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see the query plan. Indexes dramatically speed up WHERE and JOIN conditions — make sure you have indexes on columns used in joins and filters. Avoid SELECT * in production — specify columns explicitly. Filter early: put WHERE conditions that reduce row count as early in the query as possible. Use CTEs and window functions instead of correlated subqueries, which re-run for every row.

Useful SQL Patterns for Data Science

-- First purchase date per customer
SELECT customer_id, MIN(order_date) AS first_purchase
FROM orders GROUP BY customer_id;

-- Sessions from clickstream (new session if gap > 30 min)
SELECT user_id, event_time,
       SUM(CASE WHEN gap_minutes > 30 OR gap_minutes IS NULL THEN 1 ELSE 0 END)
           OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM (
    SELECT user_id, event_time,
           DATEDIFF('minute',
                    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time),
                    event_time) AS gap_minutes
    FROM clickstream
) t;

Conclusion

Advanced SQL — window functions, CTEs, recursive queries, and pivots — is what separates analysts who can answer ad-hoc questions from data scientists who can build production analytics pipelines. Master these patterns and you’ll find SQL is expressive enough to replace Python for a surprising fraction of data transformation work, with the added benefit that it runs where your data lives without any data movement.