Open navigation menu

Window Functions for Analytics

Uses window functions like ROW_NUMBER, RANK, LAG, LEAD, and running totals to perform advanced analytics without collapsing result rows.

SqlData Fetching

Code

snippet
SELECT
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank,
    RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS dept_rank,
    LAG(salary, 1) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS prev_salary,
    LEAD(salary, 1) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS next_salary,
    SUM(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM employees
ORDER BY department_id, salary DESC;

Related Examples