Top SQL Queries for Practice (With Short Answers) - Window Function Queries (61–80)

61. Assign row numbers to employees

    SELECT emp_name, ROW_NUMBER() OVER (ORDER BY salary DESC) rn
    FROM Employee;

62. Rank employees by salary

    SELECT emp_name, RANK() OVER (ORDER BY salary DESC) rnk
    FROM Employee;

63. Dense rank employees by salary

    SELECT emp_name, DENSE_RANK() OVER (ORDER BY salary DESC) drnk
    FROM Employee;

64. Find top 3 salaries

    SELECT *
    FROM (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) drnk
    FROM Employee
    ) x
    WHERE drnk <= 3;

65. Find top 3 salaries in each department

    SELECT *
    FROM (
    SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id 
    ORDER BY salary DESC) drnk
    FROM Employee
    ) x
    WHERE drnk <= 3;

66. Find highest salary in each department

    SELECT *
    FROM (
    SELECT *, RANK() OVER (PARTITION BY dept_id 
    ORDER BY salary DESC) rnk
    FROM Employee
    ) x
    WHERE rnk = 1;

67. Calculate running salary total

    SELECT emp_name, salary,
    SUM(salary) OVER (ORDER BY emp_id) running_total
    FROM Employee;

68. Calculate cumulative sales

    SELECT month, sales,
    SUM(sales) OVER (ORDER BY month) cumulative_sales
    FROM Sales;

69. Get previous salary value

    SELECT emp_name, salary,
    LAG(salary) OVER (ORDER BY emp_id) prev_salary
    FROM Employee;

70. Get next salary value

    SELECT emp_name, salary,
    LEAD(salary) OVER (ORDER BY emp_id) next_salary
    FROM Employee;

71. Compare current and previous month sales

    SELECT month, sales,
    LAG(sales) OVER (ORDER BY month) prev_month_sales
    FROM Sales;

72. Find salary difference from previous employee

    SELECT emp_name, salary,
    salary - LAG(salary) OVER (ORDER BY emp_id) diff
    FROM Employee;

73. Find first salary in each department

    SELECT *,
    FIRST_VALUE(salary) OVER (PARTITION 
    BY dept_id ORDER BY salary DESC)
    FROM Employee;

74. Find last salary in each department

    SELECT *,
    LAST_VALUE(salary) OVER (PARTITION BY 
    dept_id ORDER BY salary DESC)
    FROM Employee;

75. Find average salary by department without grouping

    SELECT emp_name, dept_id,
    AVG(salary) OVER (PARTITION BY dept_id) avg_salary
    FROM Employee;

76. Find percent contribution of salary

    SELECT emp_name, salary,
    salary * 100.0 / SUM(salary) OVER() pct
    FROM Employee;

77. Find duplicate rows using row_number

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION 
    BY emp_name ORDER BY emp_id) rn
    FROM Employee
    ) x
    WHERE rn > 1;

78. Delete duplicate rows
    Use above query in CTE and delete where rn > 1.
79. Find latest order per customer

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 
    customer_id ORDER BY order_date DESC) rn
    FROM Orders
    ) x
    WHERE rn = 1;

80. Find first order per customer

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 
    customer_id ORDER BY order_date ASC) rn
    FROM Orders
    ) x
    WHERE rn = 1;

---