SQL nâng cao: Window Functions và CTE thực chiến

SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

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

SELECT
    month, revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;

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

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
),
ranked AS (
    SELECT
        month, revenue,
        RANK() OVER (ORDER BY revenue DESC) AS rank
    FROM monthly_revenue
)
SELECT * FROM ranked WHERE rank <= 3;

WITH ranked_employees AS (
    SELECT
        e.name, d.dept_name,
        SUM(s.amount) AS total_sales,
        DENSE_RANK() OVER (PARTITION BY d.dept_name ORDER BY SUM(s.amount) DESC) AS rank
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    JOIN sales s ON e.id = s.employee_id
    GROUP BY e.name, d.dept_name
)
SELECT * FROM ranked_employees WHERE rank <= 3;