Advanced T-SQL: CTEs, window functions and APPLY for complex analysis

Many analysts know GROUP BY and JOIN, but advanced T-SQL features — recursive CTEs, window functions and APPLY — solve in a few lines problems that would otherwise require cursors or imperative code.

Recursive CTEs — hierarchies in SQL

-- Department hierarchy with level and full path
WITH CTE_Hierarchy AS (
    -- Anchor: root of the hierarchy
    SELECT dept_id, name, parent_dept_id, 0 AS level, CAST(name AS VARCHAR(500)) AS path
    FROM dbo.Departments WHERE parent_dept_id IS NULL

    UNION ALL

    -- Recursive part
    SELECT d.dept_id, d.name, d.parent_dept_id,
           h.level + 1,
           CAST(h.path + ' > ' + d.name AS VARCHAR(500))
    FROM dbo.Departments d
    JOIN CTE_Hierarchy h ON d.parent_dept_id = h.dept_id
)
SELECT * FROM CTE_Hierarchy ORDER BY path;

Window Functions — analysis without GROUP BY

SELECT
    customer_id,
    sale_date,
    amount,
    -- Ranking within each customer
    ROW_NUMBER()  OVER (PARTITION BY customer_id ORDER BY sale_date DESC) AS rn,
    -- Running total
    SUM(amount)   OVER (PARTITION BY customer_id ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS running_total,
    -- Comparison with the previous sale
    LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY sale_date) AS prev_sale,
    amount - LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY sale_date) AS variation
FROM dbo.Sales;

CROSS APPLY — joins with functions and correlated subqueries

-- Last 3 purchases of each customer (impossible with a simple JOIN)
SELECT c.name, s.sale_date, s.amount
FROM dbo.Customers c
CROSS APPLY (
    SELECT TOP 3 sale_date, amount
    FROM dbo.Sales
    WHERE customer_id = c.customer_id
    ORDER BY sale_date DESC
) s;

-- OUTER APPLY includes customers with no purchases (like LEFT JOIN)

Conclusion

CTEs improve the readability of complex queries, window functions eliminate the need for subqueries in comparative analysis, and APPLY solves top-N per group problems elegantly. Together, these three features cover 90% of advanced analysis cases in T-SQL.