What are SQL window functions? Explain ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD.

function_name() OVER (
    PARTITION BY column   -- divide into groups (optional)
    ORDER BY column       -- order within each group
    ROWS/RANGE ...        -- frame definition (optional)
)

| id | name    | department | salary |
|----|---------|-----------|--------|
|  1 | Alice   | QA        | 95000  |
|  2 | Bob     | Dev       | 120000 |
|  3 | Charlie | QA        | 85000  |
|  4 | Diana   | Dev       | 135000 |
|  5 | Eve     | QA        | 110000 |
|  6 | Frank   | Dev       | 120000 |

-- Number each row within each department by salary (desc)
SELECT name, department, salary,
       ROW_NUMBER() OVER (
           PARTITION BY department
           ORDER BY salary DESC
       ) AS row_num
FROM employees;

-- Result:
-- Alice   | QA  | 95000  | 2
-- Charlie | QA  | 85000  | 3
-- Eve     | QA  | 110000 | 1   ← highest in QA = 1
-- Bob     | Dev | 120000 | 2
-- Diana   | Dev | 135000 | 1   ← highest in Dev = 1
-- Frank   | Dev | 120000 | 3   ← same salary as Bob but different row_num

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM employees;

-- Bob and Frank both earn 120000 in Dev:
-- Diana | Dev | 135000 | 1
-- Bob   | Dev | 120000 | 2   ← tied
-- Frank | Dev | 120000 | 2   ← tied (same rank)
-- (rank 3 is SKIPPED — gap after tie)

SELECT name, department, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

-- Diana | Dev | 135000 | 1
-- Bob   | Dev | 120000 | 2   ← tied
-- Frank | Dev | 120000 | 2   ← tied
-- (next rank is 3, NOT 4 — no gap)

-- Compare each month's sales with the previous month
SELECT
    month,
    sales,
    LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_month_sales,
    sales - LAG(sales, 1, 0) OVER (ORDER BY month) AS growth
FROM monthly_sales;

-- LAG(column, offset, default)
-- LAG(sales, 1, 0) → previous row's sales, 0 if no previous row

SELECT
    month,
    sales,
    LEAD(sales, 1) OVER (ORDER BY month) AS next_month_sales
FROM monthly_sales;
-- Shows what the next month will be for each row

-- Using ROW_NUMBER to get top 1 per department
SELECT name, department, salary
FROM (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

-- Result:
-- Eve   | QA  | 110000
-- Diana | Dev | 135000

SELECT order_id, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;