Skip to content

Leetcode: SQL Interview Prep Guide

Jump to bottom
Praveen Kumar Anwla edited this page Feb 15, 2025 · 17 revisions

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;

Method 2:

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

)

SELECT Department , Employee, Salary
FROM CTE
Where rnk<=3

Q4. Nth Highest Salary

Ans:

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (
    WITH cte AS (
      SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
      FROM employee
    )
    SELECT salary 
    FROM cte
    WHERE rnk = N
    LIMIT 1 --If there're more than 1 highest salary (e.g., 100K, 100K )
  );
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;

Method 2

WITH CTE AS (
    SELECT 
    d.name as Department , e.name as Employee, e.salary as Salary,
    DENSE_RANK() OVER(PARTITION BY d.id ORDER BY Salary DESC) as rnk
    FROM Employee e JOIN  Department d ON e.departmentId =d.id

)

SELECT Department , Employee, Salary
FROM CTE
Where rnk=1

Q6. Duplicate Emails: Write a solution to report all the duplicate emails. Note that it's guaranteed that the email field is not NULL. Return the result table in any order.

Answer: 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.

Method 1

SELECT DISTINCT P1.Email FROM Person P1,Person P2 

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

Example to demonstrate. Note: Compare it with later question.


### Initial `Person` Table:

| id | Email           |
|----|-----------------|
| 1  | alice@example.com |
| 2  | bob@example.com   |
| 3  | alice@example.com |
| 4  | carol@example.com |
| 5  | bob@example.com   |

**1. Result After Join Operation (Cartesian Join)**:
Let's illustrate a few rows from the full Cartesian join (not showing all combinations for brevity):

| P1.id | P1.Email           | P2.id | P2.Email           |
|-------|--------------------|-------|--------------------|
| 1     | alice@example.com  | 2     | bob@example.com    |
| 1     | alice@example.com  | 3     | alice@example.com  |
| 2     | bob@example.com    | 1     | alice@example.com  |
| 2     | bob@example.com    | 5     | bob@example.com    |
| 3     | alice@example.com  | 1     | alice@example.com  |
| 5     | bob@example.com    | 2     | bob@example.com    |
| ...   | ...                | ...   | ...                |

**2, Filtering results**
After the cartesian join, We filter where `P1.id <> P2.id` and `P1.Email = P2.Email`.

| P1.id | P1.Email           | P2.id | P2.Email           |
|-------|--------------------|-------|--------------------|
| 1     | alice@example.com  | 3     | alice@example.com  |
| 3     | alice@example.com  | 1     | alice@example.com  |
| 2     | bob@example.com    | 5     | bob@example.com    |
| 5     | bob@example.com    | 2     | bob@example.com    |


**Final Output** (After applying `DISTINCT`):
| Email             |
|-------------------|
| alice@example.com |
| bob@example.com   |

Method 2

# Write your MySQL query statement below
WITH CTE AS (

    SELECT email,
    ROW_NUMBER() OVER(PARTITION BY email order by id ASC) as rnk
    FROM Person

)

SELECT email
FROM CTE
WHERE rnk=2

Method 3: But it'll take longer processing time.

# Write your MySQL query statement below
WITH CTE AS (

    SELECT email,
    ROW_NUMBER() OVER(PARTITION BY email order by id ASC) as rnk
    FROM Person

)

SELECT distinct email
FROM CTE
WHERE rnk>1

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;
**1. Filtering Results** (with `p1.id > p2.id` and `p1.email = p2.email`):

| P1.id | P1.Email           | P2.id | P2.Email           |
|-------|--------------------|-------|--------------------|
| 3     | alice@example.com  | 1     | alice@example.com  |
| 5     | bob@example.com    | 2     | bob@example.com    |

**2. Effect on Table**:
This would delete rows with IDs 3 and 5 because they have higher IDs than other rows with the same email.

**Updated `Person` Table**:
| id | Email           |
|----|-----------------|
| 1  | alice@example.com |
| 2  | bob@example.com   |
| 4  | carol@example.com |

Q10. Customers Who Never Order

Method 1:

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

Method 2:

SELECT name as Customers
FROM Customers C LEFT JOIN Orders  o on C.id=O.customerId 
WHERE o.customerId  IS NULL

Q11. Replace Employee ID With The Unique Identifier: Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.

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: Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.

Note: For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.

Ans:

SELECT A.employee_id , A.name, 
count(B.employee_id) AS reports_count,
ROUND(AVG(B.age)) as average_age 
FROM Employees A JOIN Employees B ON A.employee_id = B.reports_to
GROUP BY A.employee_id , A.name
ORDER BY 1
Clone this wiki locally