🧠 SQL Interview Question (Moderate–Tricky & Duplicate Detection + Latest Record)
📌

employees(emp_id, email, updated_at)

❓ Ques :

👉 Find duplicate emails, but return only the latest record for each duplicate email.

🧩 How Interviewers Expect You to Think

• Identify duplicates using COUNT() 📊
• Use window functions for ranking
• Partition by email
• Order by latest timestamp
• Filter only duplicates + latest row

💡 SQL Solution

SELECT emp_id, email, updated_at
FROM (
SELECT
emp_id,
email,
updated_at,
COUNT(*) OVER (PARTITION BY email) AS cnt,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY updated_at DESC
) AS rn
FROM employees
) t
WHERE cnt > 1
AND rn = 1;

🔥 Why This Question Is Powerful

• Tests window functions (COUNT OVER, ROW_NUMBER) 🧠
• Combines deduplication + ranking logic
• Very common in data cleaning scenarios 🧹
• Real-world use case: keeping latest user records

❤️ React if you want more such real interview-level SQL questions 🚀