SQL QUERY QUESTIONS - prkirankumar/interview-preparation GitHub Wiki

1. Find nth highest salary?

Using OFFSET

SELECT DISTINCT salary FROM table ORDER BY salary DESC LIMIT 1 OFFSET 1

Using CTE

with ordered_salary as
(
SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) rn
FROM salary_table
)
select name, salary
from ordered_salary
where rn = 5

2. Department wise highest Salary

Using GroupBy

select dept, max(salary) from Employee
group by dept

Using Windows Function to find nth highest salary department wise (Here Partition by will work like grouping)

select * from (select *, ROW_NUMBER() Over(partition by dept order by salary desc) as highest_payed from Employee)
where highest_payed = 1

3.Display Alternate Records (even number)

Select * from (Select *,
ROW_NUMBER() OVER(order by salary) as rn from Employee)
where rn % 2 =0

4. Display Duplicate records

Windows Function method

select * from (
select emp_name,
ROW_NUMBER() OVER(PARTITION by emp_name) rn
from Employee)
where rn > 1

Having Method

select emp_name, count(*) from employee group by emp_name HAVING count(*) > 1

5. Find nth column in a table (using offset number)

select * from Customers
limit 1
offset 3

6. First n and last m rows

WITH CTE AS (
SELECT *, row_number() OVER(ORDER BY pk of that table) as rn, count(*) OVER () AS total_rows
FROM table1)
SELECT *
FROM CTE
WHERE rn <=n OR rn > total_rows - m

7. ROW_NUMBER, RANK, DENSE_RANK

RANK() will give rank like 1 2 3 3 5

Row_NUMBER() will give unique number even if there is a tie like 1 2 3 4 5

Dense_Rank() will rank but it will not skip rank if there is a tie like 1 2 3 3 4

8. How to Check if a User/record Exists Among Billions of records in efficient way?

⚠️ **GitHub.com Fallback** ⚠️