SQL Interview Series — Part 17 📌 Question 17: Find the Second-Highest Salary in Each Department Suppose you have an Employee table: employee_id | employee_name | department | salary ------------|---------------|------------|------- 1 | Amit | IT | 50000 2 | Rahul | IT | 90000 3 | Priya | IT | 70000 4 | Neha | HR | 60000 5 | Raj | HR | 80000 6 | Karan | HR | 100000 ❓ Question Find the second-highest distinct salary in each department. 💡 Approach We need to rank salaries separately within each department. Use: DENSE_RANK() with: PARTITION BY department ORDER BY salary DESC 📌 SQL Solution SELECT employee_id, employee_name, department, salary FROM ( SELECT employee_id, employee_name, department, salary, DENSE_RANK() OVER ( PARTITION BY department ORDER BY salary DESC ) AS salary_rank FROM Employee ) e WHERE salary_rank = 2; 🔎 How does it work? For IT: Rahul → 90000 → Rank 1 Priya → 70000 → Rank 2 Amit → 50000 → Rank 3 For HR: Karan → 100000 → Rank 1 Raj → 80000 → Rank 2 Neha → 60000 → Rank 3 Therefore: employee_name | department | salary --------------|------------|------- Priya | IT | 70000 Raj | HR | 80000 ⚠️ Why use DENSE_RANK() instead of ROW_NUMBER()? Suppose IT has: 90000 80000 80000 70000 With DENSE_RANK(): 90000 → Rank 1 80000 → Rank 2 80000 → Rank 2 70000 → Rank 3 Both employees earning 80,000 are returned. If the interviewer asks for the second-highest distinct salary, DENSE_RANK() is usually the safest choice. Double Tap ❤️ For More

𝗧𝗢𝗣 𝟱 𝗙𝗥𝗘𝗘 𝗧𝗘𝗖𝗛 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝗜 𝗪𝗜𝗦𝗛 𝗜 𝗛𝗔𝗗 𝗧𝗔𝗞𝗘𝗡 𝗦𝗢𝗢𝗡𝗘𝗥! 💻🎓 Discover 5 FREE tech courses to kick-start your learning journey and strengthen your resume. 🚀 ✅ Learn Valuable Technical Skills ✅ Beginner-Friendly Learning Resources ✅ Build Practical Knowledge ✅ Strengthen Your Resume & Portfolio ✅ Prepare for Tech Career Opportunities 💡 The best investment you can make in 2026 is upgrading your skills! 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :- https://pdlink.in/4Bw1PL9 ⚡ Start learning today. Your future self will thank you!

SQL Interview Series — Part 16 📌 Question 16: Find and Remove Duplicate Records While Keeping the Latest Record Suppose you have a CustomerOrders table: order_id | customer_id | order_date | amount ---------|-------------|-------------|------- 101 | 1 | 2026-01-05 | 500 102 | 1 | 2026-02-10 | 800 103 | 2 | 2026-01-15 | 400 104 | 2 | 2026-03-01 | 900 105 | 3 | 2026-02-20 | 700 Assume each customer_id should have only one record, and we want to keep the latest record for each customer. ❓ Question Identify the duplicate records that should be removed while keeping the latest order for every customer. 💡 Approach Use ROW_NUMBER() to rank each customer's records: • Latest record → rn = 1 • Older records → rn > 1 📌 Find the records to remove SELECT order_id, customer_id, order_date, amount FROM ( SELECT order_id, customer_id, order_date, amount, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC ) AS rn FROM CustomerOrders ) x WHERE rn > 1; 🔎 Result order_id | customer_id | order_date ---------|-------------|------------ 101 | 1 | 2026-01-05 103 | 2 | 2026-01-15 These are the older records. The records we keep are: 102 → Customer 1 → 2026-02-10 104 → Customer 2 → 2026-03-01 105 → Customer 3 → 2026-02-20 🧠 Why ROW_NUMBER()? ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY order_date DESC ) For Customer 1: order_id | order_date | rn ---------|-------------|--- 102 | 2026-02-10 | 1 101 | 2026-01-05 | 2 So: rn = 1 → Keep rn > 1 → Duplicate/older record Double Tap ❤️ For More