🔥 Recent Data Analyst Interview Q&A at Deloitte 🔥

Question:
👉 Write an SQL query to extract the third highest salary from an employee table with columns EID and ESalary.

Solution:

SELECT ESalary  
FROM (  
  SELECT ESalary,  
         DENSE_RANK() OVER (ORDER BY ESalary DESC) AS salary_rank  
  FROM employee  
) AS ranked_salaries  
WHERE salary_rank = 3;

Explanation of the Query:

1️⃣ Step 1: Create a Subquery

The subquery ranks all salaries in descending order using DENSE_RANK().

2️⃣ Step 2: Rank the Salaries

Assigns ranks: 1 for the highest salary, 2 for the second-highest, and so on.

3️⃣ Step 3: Assign an Alias

The subquery is given an alias (ranked_salaries) to use in the main query.

4️⃣ Step 4: Filter for the Third Highest Salary

The WHERE clause filters the results to include only the salary with rank 3.

5️⃣ Step 5: Display the Third Highest Salary

The main query selects and displays the third-highest salary.

By following these steps, you can easily extract the third-highest salary from the table.



#DataAnalyst #SQL #InterviewTips