Need Help with SQL Query Interview Questions

Language Help SQL
1

Hello fellow developers,

I hope you’re all doing well. I am currently preparing for SQL interviews and came across a valuable resource that I’d like to share with all of you. I’ve been using SQL Query Interview Questions page to practice, and it has been quite helpful in sharpening my SQL skills.

While working through the questions, I encountered a particular challenge that I could use some assistance with. The question involves complex SQL querying, and I’ve been struggling to find the right approach to solve it efficiently. Here’s the problem statement:

[sql query that detects managers with atleast 5 direct reports from the employee table]

I have attempted the query, but it’s not producing the desired results. I believe there might be an issue with my logic or syntax. That’s why I’m reaching out to this fantastic community for some guidance.

If any of you have experience with SQL and enjoy tackling challenging queries, I would really appreciate your input. Whether you provide a full solution or just some tips to get me going in the right direction, it would be of immense help.

Thank you all in advance!

2

Assuming the provided query is correct:

SELECT Name
FROM Employee
WHERE id IN
  (SELECT ManagerId
    FROM Employee
    GROUP BY ManagerId
    HAVING COUNT(DISTINCT Id) >= 5);

It’s sub optimal. Getting rid of an IN is almost always an optimization.

Here’s a query that should offer the number of empoloyees per manager:

SELECT ManagerId, count(*) as Employees
    FROM Employee
    GROUP BY ManagerId

Given that, you can write the answer as:

SELECT e.Name as ManagerName, m.Employees
    FROM Employee e
        join (
                SELECT ManagerId, count(*) as Employees
                FROM Employee
                GROUP BY ManagerId
            ) m on e.Id=m.ManagerId

For the >=5 you can do a where or the same having on the sub query.