SQL Queries- Aggregation Functions, Window Functions, Ranking Functions

 

Category	Query Example

Aggregation

GROUP BY, SUM, AVG, COUNT, MAX, MIN

Window Functions

LAG(), LEAD(), SUM() OVER(), RANK(), PERCENTILE_CONT

Ranking

RANK() OVER(), DENSE_RANK(), ROW_NUMBER()

📊 Aggregation Queries

(Using GROUP BY, COUNT, MAX, SUM, AVG, etc.)

1. Get the count of duplicates in a table

SELECT column_name, COUNT(*)
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;

2. Count occurrences of each word in a table column

SELECT word, COUNT(*) AS occurrences
FROM words
GROUP BY word;

3. Write a SQL query to get the department-wise highest salary

SELECT department_id, MAX(salary) AS Max_Salary
FROM employees
GROUP BY department_id;

4. Write a SQL query to find the average salary of employees by department

SELECT department_id, AVG(salary) AS Avg_Salary
FROM employees
GROUP BY department_id;

5. Write a SQL query to calculate the median salary of employees
   (if your DB supports PERCENTILE_CONT, e.g., PostgreSQL, SQL Server)

SELECT DISTINCT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS Median_Salary
FROM employees;

6. Write a SQL query to display the cumulative sum of a column

SELECT employee_name, salary,
       SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;

---

🪟 Window Functions Queries

(Using RANK, DENSE_RANK, LAG, LEAD, SUM() OVER, etc.)

1. Use LAG and RANK functions in a query

SELECT employee_name, salary, 
       LAG(salary) OVER (ORDER BY salary DESC) AS Previous_Salary,
       RANK() OVER (ORDER BY salary DESC) AS Salary_Rank
FROM employees;

2. Write a SQL query to rank employees based on their performance score

SELECT employee_name, performance_score,
       RANK() OVER (ORDER BY performance_score DESC) AS Rank
FROM employees;

3. Write a SQL query to find the top 3 earners in each department

SELECT department_id, employee_name, salary
FROM (
    SELECT department_id, employee_name, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank <= 3;

4. Write a SQL query to find the nth highest salary in a table

SELECT DISTINCT salary
FROM employees e1
WHERE N - 1 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);

(replace N with the rank you want)

5. Write a SQL query to calculate the median salary of employees

SELECT DISTINCT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS Median_Salary
FROM employees;

6. Write a SQL query to display the cumulative sum of a column

SELECT employee_name, salary,
       SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;

---

🏆 Ranking Queries

(Specific to RANK/DENSE_RANK/ROW_NUMBER)

1. Write a SQL query to find the top 3 earners in each department

SELECT department_id, employee_name, salary
FROM (
    SELECT department_id, employee_name, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank <= 3;

2. Use RANK function to rank employees based on their performance score

SELECT employee_name, performance_score,
       RANK() OVER (ORDER BY performance_score DESC) AS Rank
FROM employees;

3. Use RANK to find nth highest salary

SELECT DISTINCT salary
FROM employees e1
WHERE N - 1 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);





1. Find the second-highest salary in a table without using LIMIT or TOP.
SELECT MAX(salary) FROM table WHERE salary NOT IN (SELECT MAX(salary) FROM table)

2. Write a SQL query to find all employees who earn more than their managers.
SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.manager_id = (link unavailable) WHERE e1.salary > e2.salary

3. Find the duplicate rows in a table without using GROUP BY.
SELECT * FROM table WHERE rowid IN (SELECT rowid FROM table GROUP BY column HAVING COUNT(*) > 1)

4. Write a SQL query to find the top 10% of earners in a table.
SELECT * FROM table WHERE salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) FROM table)

5. Find the cumulative sum of a column in a table.
SELECT column, SUM(column) OVER (ORDER BY rowid) FROM table

6. Write a SQL query to find all employees who have never taken a leave.
SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM leaves)

7. Find the difference between the current row and the next row in a table.
SELECT *, column - LEAD(column) OVER (ORDER BY rowid) FROM table

8. Write a SQL query to find all departments with more than one employee.
SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 1

9. Find the maximum value of a column for each group without using GROUP BY.
SELECT MAX(column) FROM table WHERE column NOT IN (SELECT MAX(column) FROM table GROUP BY group_column)

10. Write a SQL query to find all employees who have taken more than 3 leaves in a month.