🧠 SQL Interview Question (Top-N per Group + Tie Handling - Tricky)
📌

scores(student_id, subject, marks)

❓ Ques :

👉 Find students who scored the highest marks in each subject.

👉 If multiple students have the same top score, include all of them (handle ties).

🧩 How Interviewers Expect You to Think

• Group data by subject 📚
• Identify highest marks within each group
• Handle ties correctly (don’t lose rows)
• Use window functions (RANK vs ROW_NUMBER)

💡 SQL Solution

SELECT
student_id,
subject,
marks
FROM (
SELECT
scores.student_id,
scores.subject,
scores.marks,
RANK() OVER (
PARTITION BY scores.subject
ORDER BY scores.marks DESC
) AS rnk
FROM scores
) ranked_scores
WHERE ranked_scores.rnk = 1;

🔥 Why This Question Is Powerful

• Tests Top-N per group (very common pattern) 🧠
• Checks understanding of RANK vs ROW_NUMBER

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