Most Complex SQL Interview Question for database testing | QA SDET
🧠 Complex SQL Query Interview Summary
1. Employees Earning Above Department Average
→ Use a correlated subquery comparing each employee's salary with the average salary of their department.
2. Top 2 Salaries per Department
→ Use DENSE_RANK() window function partitioned by department_id, filter where rank 2.
3. Departments with More Than 5 Employees
→ Group by department_id and apply HAVING COUNT(*) 5.
4. Employees with Same Salary in Same Department
→ Use EXISTS with a self-join condition to check for same salary & department but different ID.
5. Manager(s) with Most Direct Reports
→ Group by manager_id, count reports, and compare with the max count using a subquery.