Leetcode: SQL Interview Prep Guide

Q1. Employees Earning More Than Their Managers

Ans:

# Write your MySQL query statement below

SELECT EMP.name AS Employee FROM Employee EMP,Employee MGR
WHERE EMP.managerId=MGR.id AND EMP.salary>MGR.salary

Q2. Managers with at Least 5 Direct Reports

Ans:

# Method1 :

SELECT E1.name
FROM Employee E1
JOIN (
    SELECT managerId, COUNT(*) AS directReports
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(*) >= 5
) E2 ON E1.id = E2.managerId;


# Method 2

SELECT e.name
FROM Employee AS e 
INNER JOIN Employee AS m ON e.id = m.managerId 
GROUP BY e.name
HAVING COUNT(m.managerId) >= 5;


# Method 3

SELECT name 
FROM Employee 
WHERE id IN (
    SELECT managerId 
    FROM Employee 
    GROUP BY managerId 
    HAVING COUNT(*) >= 5)

# Method 4

SELECT a.name 
FROM Employee a 
JOIN Employee b ON a.id = b.managerId 
GROUP BY b.managerId 
HAVING COUNT(*) >= 5

# Method 5
SELECT e1.name
FROM employee e1
LEFT JOIN employee e2 ON e1.id=e2.managerId
GROUP BY e1.id
HAVING COUNT(e2.name) >= 5;

### Initial `Employee` Table:

| id | name       | managerId |
|----|------------|-----------|
| 1  | Alice      | NULL      |
| 2  | Bob        | 1         |
| 3  | Charlie    | 1         |
| 4  | David      | 1         |
| 5  | Eve        | 1         |
| 6  | Fiona      | 1         |
| 7  | George     | 3         |
| 8  | Hannah     | 3         |

### Step 1: Result After Join Operation
This result shows the pairing of each manager with their direct reports. We're joining the `Employee` table with itself based on `e.id = m.managerId`.

**Resultant Table:**

| ManagerName | EmployeeName | managerId |
|-------------|--------------|-----------|
| Alice       | Bob          | 1         |
| Alice       | Charlie      | 1         |
| Alice       | David        | 1         |
| Alice       | Eve          | 1         |
| Alice       | Fiona        | 1         |
| Charlie     | George       | 3         |
| Charlie     | Hannah       | 3         |

### Step 2: Result After `GROUP BY` and `HAVING` Clause
Now, we will group this result by the manager's name and count how many direct reports each manager has. We will then filter to show only those managers with 5 or more direct reports.


**Final Resultant Table:**

| ManagerName |
|-------------|
| Alice       |

This table shows that Alice is the only manager with 5 or more direct reports based on the `HAVING COUNT(m.managerId) >= 5` condition, which matches the description provided. This result effectively filters out managers like Charlie, who only manages 2 employees.

Q3. Department Top Three Salaries

Ans:

-- Write your MySQL query statement below
SELECT Department, Employee, Salary
FROM (
    SELECT 
        d.name AS Department,
        e.name AS Employee,
        e.salary AS Salary,
        DENSE_RANK() OVER (PARTITION BY d.name ORDER BY Salary DESC) AS rnk
    FROM Employee e
    JOIN Department d
    ON e.departmentId = d.id
) AS rnk_tbl
WHERE rnk <= 3;

Q4. Nth Highest Salary

Ans:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
    WITH RankedSalaries AS 
    (
      SELECT salary, 
             DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
      FROM employee
    )
    SELECT salary
    FROM RankedSalaries
    WHERE rank = N
    LIMIT 1
  );
END

To use this function, you would call it like so:

SELECT getNthHighestSalary(3);

Q5. Department Highest Salary : Write a solution to find employees who have the highest salary in each of the departments.

# Write your MySQL query statement below
SELECT 
    Department,
    Employee,
    Salary
FROM (SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary,
    DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rnk
FROM Employee e
JOIN Department d
ON e.departmentId = d.id) AS t
WHERE rnk = 1;

Q6. Duplicate Emails

The query performs a Cartesian join of the Person table with itself. This query uses <> (not equal to) to find any pairs of rows that have the same email but are not the same row, which is useful just for identification of duplicates.

SELECT DISTINCT P1.Email FROM Person P1,Person P2 

WHERE P1.id <> P2.id AND P1.Email=P2.Email

Q7. Rank Scores

Ans:

SELECT score, dense_rank() OVER(ORDER BY score DESC) as "rank"
FROM Scores

Q8. Customer Who Visited but Did Not Make Any Transactions

Ans:

# Write your MySQL query statement below

SELECT v.customer_id, COUNT(v.visit_id) AS count_no_trans 
from Visits v 
LEFT JOIN Transactions t 
ON v.visit_id = t.visit_id  
WHERE t.transaction_id IS NULL 
GROUP BY v.customer_id; 


#UPDATE DEC 1ST : we take the t.amount as null
SELECT v.customer_id, COUNT(v.visit_id) AS count_no_trans 
from Visits v 
LEFT JOIN Transactions t 
ON v.visit_id = t.visit_id  
WHERE t.amount IS NULL 
GROUP BY v.customer_id; 

Q9. Delete Duplicate Emails

Ans: The query performs a Cartesian join of the Person table with itself. This query uses < (or >) to not only identify duplicates but also to choose one of the records based on the id value for deletion.

delete p1 from person p1,person p2 
where p1.email=p2.email and p1.id>p2.id;

Q10. Customers Who Never Order

SELECT name as Customers
from Customers
where id not in (
    select customerId
    from Orders
);

Q11. Replace Employee ID With The Unique Identifier

Ans:

# Write your MySQL query statement below
select 
eu.unique_id as unique_id, e.name as name
from Employees e left join EmployeeUNI eu on e.id = eu.id

Q12. The Number of Employees Which Report to Each Employee

Ans:

SELECT
    emp1.employee_id,
    emp1.name,
    COUNT(emp2.employee_id) AS reports_count,
    ROUND(AVG(emp2.age)) AS average_age
FROM Employees emp1
INNER JOIN Employees emp2 ON emp1.employee_id = emp2.reports_to
GROUP BY emp1.employee_id
ORDER BY emp1.employee_id