Window Functions

-- ROW_NUMBER() - unique row number
SELECT
    name, marks, dept_id,
    ROW_NUMBER() OVER (ORDER BY marks DESC) AS rank_overall
FROM students;

-- RANK() - same rank for ties
SELECT
    name, marks, dept_id,
    RANK() OVER (ORDER BY marks DESC) AS rank_overall,
    RANK() OVER (PARTITION BY dept_id ORDER BY marks DESC) AS rank_in_dept
FROM students;

-- LEAD and LAG - access next/previous row
SELECT
    name, marks,
    LAG(marks, 1)  OVER (ORDER BY id) AS prev_marks,
    LEAD(marks, 1) OVER (ORDER BY id) AS next_marks
FROM students;

-- Running total
SELECT
    name, marks,
    SUM(marks) OVER (ORDER BY id) AS running_total,
    AVG(marks) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM students;