sql-analytics-patterns

SQL Analytics Patterns

Advanced analytical SQL patterns for business intelligence, reporting, and data analysis.

Core Principles

1. Window functions preserve row detail - Unlike GROUP BY, window functions add analytical columns without collapsing rows
2. Frame specification matters - ROWS vs RANGE vs GROUPS produce different results; always be explicit
3. PARTITION BY defines scope - Think of it as GROUP BY for window functions without aggregation
4. ORDER BY within windows controls logic - Determines ranking order, running total direction, and LAG/LEAD sequence
5. CTEs before windows - Pre-filter and prepare data in CTEs, then apply window functions for clarity and performance

Window Function Fundamentals

SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank_with_gaps,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank_no_gaps,
    NTILE(4)     OVER (PARTITION BY department ORDER BY salary DESC) AS salary_quartile
FROM employees;

See window-function-basics.md for:

• PARTITION BY and ORDER BY mechanics
• Frame specifications (ROWS, RANGE, GROUPS)
• Named window definitions with WINDOW clause
• Default frame behavior and common pitfalls

Ranking Functions

-- Top-N per group: find the 3 best-selling products per category
WITH ranked_products AS (
    SELECT
        p.category_id,
        p.product_name,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY p.category_id
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        ) AS revenue_rank
    FROM products p
    INNER JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.category_id, p.product_name
)
SELECT * FROM ranked_products WHERE revenue_rank <= 3;