SQL Window Functions

You are a SQL analyst specializing in analytical queries. The user wants to master window functions (ROW_NUMBER, RANK, LAG, LEAD) to perform advanced data analysis without GROUP BY aggregation.

What to check first

• Verify your database supports window functions (PostgreSQL 8.4+, MySQL 8.0+, SQL Server 2012+, SQLite 3.25+)
• Run SELECT version(); to confirm your SQL engine version
• Ensure you have a table with multiple rows and a clear ordering column (date, ID, timestamp)

Steps

1. Understand the OVER() clause syntax — every window function requires OVER(PARTITION BY column ORDER BY column) to define the window frame
2. Use ROW_NUMBER() to assign unique sequential integers within each partition, reset per PARTITION BY group
3. Use RANK() when you need gaps for ties (two records with same value get ranks 1,1,3 not 1,1,2)
4. Use DENSE_RANK() for consecutive ranks without gaps (1,1,2 instead of 1,1,3)
5. Use LAG() to access the previous row's value in the same partition — requires LAG(column, offset, default) OVER(ORDER BY column)
6. Use LEAD() to peek at the next row — mirror syntax of LAG but forward-looking
7. Combine multiple window functions in SELECT and use them in WHERE subqueries or CTEs
8. Order the final result set separately from the window frame ordering — window ORDER BY is independent of final output order

Code

WITH sales_data AS (
  SELECT 
    employee_id,
    sale_date,
    amount,
    department,
    -- Assign unique row number per employee, ordered by date
    ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY sale_date) AS sale_sequence,
    
    -- Rank by amount within department (ties get same rank, next rank skips)
    RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS amount_rank,
    
    -- Dense rank (no gaps for ties)
    DENSE_RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dense_amount_rank,
    
    -- Get previous sale amount for same employee
    LAG(amount, 1, 0) OVER (PARTITION BY employee_id ORDER BY sale_date) AS prev_sale,
    
    -- Get next sale amount for same employee
    LEAD(amount, 1, 0) OVER (PARTITION BY employee_id ORDER BY sale_date) AS next_sale,
    
    -- Calculate sale growth vs previous sale
    amount - LAG(amount, 1, 0) OVER (PARTITION BY employee_id ORDER BY sale_date) AS sale_growth,
    
    -- Running sum of sales per employee
    SUM(amount) OVER (PARTITION BY employee_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change