Home › Interview Questions › Sql, Cte, Row Number

Use a CTE with ROW_NUMBER() to rank salaries and filter for the second highest.

Sql Cte Row Number
🟡 Medium Coding Junior level
1 Times asked
Aug 2026 Last seen
Aug 2026 First seen

💡 Model Answer

To find the second highest salary using a CTE and ROW_NUMBER, you rank all rows by salary in descending order and then pick the row with rank 2. This approach handles ties correctly because ROW_NUMBER assigns a unique rank to each row. The query is:

WITH RankedSalaries AS (

 
SELECT employee_id,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees

)

SELECT employee_id, salary

FROM RankedSalaries

WHERE rn = 2;

Complexity: The window function scans the table once, so the time complexity is O(n) plus the sort cost, which is typically O(n log n). The space complexity is O(n) for the temporary result set. This method is portable across most SQL engines that support window functions.

Sign in to unlock the rest of this answer

Login Sign Up Free

This answer was generated by AI for study purposes. Use it as a starting point — personalize it with your own experience.

🎤 Get questions like this answered in real-time

Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.

Get Assisting AI — Starts at ₹500