Top SQL Interview Question

Question 1: Combine Two Tables

Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

Answer:

SELECT
    p.first_name,
    p.last_name,
    a.city,
    a.state
FROM
    Person p
LEFT JOIN
    Address a ON p.personId = a.personId;

Question 2: Second Highest Salary

Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

Answer:

Solution #1: Using a subquery with the LIMIT clause

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

Solution #2: Using a subquery with the MAX function:

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Solution #3: Using a subquery with the NOT IN clause:

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);

Solution #4: Using a subquery with the RANK() function:

SELECT Salary AS SecondHighestSalary
FROM (
 SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
 FROM Employee
) AS RankedSalaries
WHERE SalaryRank = 2;

Question 3: Nth highest salary

Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

Answer: Certainly! Here are several ways you can write an SQL query to find the nth highest salary from the Employee table:

Solution #1: Using the LIMIT clause with OFFSET:

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET n-1;

Solution #2: Using a subquery with the OFFSET and FETCH clauses (if supported by your database):

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
OFFSET n-1 ROWS
FETCH NEXT 1 ROWS ONLY;

Solution #3: Using a subquery with the RANK() function:

SELECT Salary
FROM (
 SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
 FROM Employee
) AS RankedSalaries
WHERE SalaryRank = n;

Solution #4: Using a subquery with the OFFSET and FETCH NEXT clauses (if supported):

SELECT Salary
FROM (
 SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber
 FROM Employee
) AS RankedSalaries
WHERE RowNumber = n;

Solution #5: Using a subquery with the NOT IN clause:

SELECT MAX(Salary) AS NthHighestSalary
FROM Employee
WHERE Salary NOT IN (
 SELECT DISTINCT TOP (n-1) Salary
 FROM Employee
 ORDER BY Salary DESC
);

Question 4: Rank Scores

Write an SQL query to rank the scores. The ranking should be calculated according to the following rules.

• The scores should be ranked from the highest to the lowest
• If there is a tie between two scores, both should have the same ranking.
• After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks.

Return the result table ordered by score in descending order.

Answer:

Solution #1: For each score in the table Scores, we count how many scores are larger than or equal to itself.

select s1.Score, 
       (select count(distinct Score) 
                from Scores s2 WHERE s2.Score >= s1.Score)as 'Rank'
from Scores s1
oerder by s1.Score desc

Solution #2: Using dense_rank() to calculate the rank of a row in an ordered set of rows.

select Score, dense_rank() over(order by Score desc) as 'Rank' from Scores

Solution #3: Using row_number() to display the number of the given row, starting at 1.

select s.Score, l.Rank 
from Scores s
left join (
    select Score, row_number() over (order by Score desc) as 'Rank' 
    from Scores 
    group by Score
) l on l.Score=s.Score
order by s.Score desc

Question 5: Consecutive Numbers

Write an SQL query to find all numbers that appear at least three times consecutively.

Answer:

To find all numbers that appear at least three times consecutively, you can use self-joins, window functions, or subqueries. Here are several possible ways to write the SQL query:

Solution #1: Using a self-join:

SELECT DISTINCT num
FROM logs l1
JOIN logs l2 ON l1.id = l2.id - 1
JOIN logs l3 ON l2.id = l3.id - 1
WHERE l1.num = l2.num AND l2.num = l3.num;

Solution #2: Using a window function:

SELECT DISTINCT num
FROM (
    SELECT num,
           LEAD(num, 1) OVER (ORDER BY id) AS next_num,
           LEAD(num, 2) OVER (ORDER BY id) AS next_next_num
    FROM logs
) AS sub
WHERE num = next_num AND num = next_next_num;

Solution #3: Using a subquery with EXISTS:

SELECT DISTINCT num
FROM logs l1
WHERE EXISTS (
    SELECT 1
    FROM logs l2
    WHERE l1.id = l2.id - 1 AND l1.num = l2.num
    AND EXISTS (
        SELECT 1
        FROM logs l3
        WHERE l2.id = l3.id - 1 AND l2.num = l3.num
    )
);

Solution #4: Using a subquery with GROUP BY and HAVING:

SELECT num
FROM (
    SELECT num, 
           COUNT(*) AS consecutive_count
    FROM (
        SELECT num,
               id - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS grp
        FROM logs
    ) AS sub
    GROUP BY num, grp
    HAVING COUNT(*) >= 3
) AS sub2;

Question 6: Employees Earning More Than Their Managers

Write an SQL query to find the employees who earn more than their managers.

Answer:

Solution #1: Using a self-join:

SELECT e.*
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Solution #2: Using a subquery:

SELECT *
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees AS manager
    WHERE employees.manager_id = manager.employee_id
);

Question 7: Duplicate Emails

Write an SQL query to report all the duplicate emails. Note that it’s guaranteed that the email field is not NULL.

To report all duplicate emails, you can use the GROUP BY clause along with the HAVING clause to filter out the duplicates. Here are several possible ways to write the SQL query:

Solution #1: Using GROUP BY and HAVING:

SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Solution #2. Using a subquery:

SELECT email
FROM (
    SELECT email, COUNT(*) AS count
    FROM users
    GROUP BY email
) AS sub
WHERE count > 1;

Solution #3. Using EXISTS:

SELECT DISTINCT email
FROM users u1
WHERE EXISTS (
    SELECT 1
    FROM users u2
    WHERE u1.email = u2.email
    AND u1.id <> u2.id
);

Solution #4. Using a self-join:

SELECT DISTINCT u1.email
FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id <> u2.id;

Question 8: Customers Who Never Order

Write an SQL query to report all customers who never order anything.

Answer:

SELECT customers.customer_id, customers.customer_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL;

Question 9: Department Highest Salary

Write an SQL query to find employees who have the highest salary in each of the departments.

Answer:

Solution #1. Using a correlated subquery:

SELECT e.*
FROM employees e
WHERE (e.department_id, e.salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

Solution #2. Using a window function (ROW_NUMBER()):

WITH RankedEmployees AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
    FROM employees
)
SELECT *
FROM RankedEmployees
WHERE row_num = 1;

Solution #3. Using a common table expression (CTE) with a self-join:

WITH MaxSalaries AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN MaxSalaries ms ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

Solution #4. Using the MAX() function with a GROUP BY clause:

SELECT e.*
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) AS max_salaries ON e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;

Question 10: Department Top Three Salaries

A company’s executives are interested in seeing who earns the most money in each of the company’s departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.
Write an SQL query to find the employees who are high earners in each of the departments.

Answer:

SELECT e.*
FROM employees e
WHERE (e.department_id, e.salary) IN (
    SELECT department_id, salary
    FROM (
        SELECT department_id, salary,
               ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
        FROM employees e2
        WHERE e.department_id = e2.department_id
    ) AS ranked_salaries
    WHERE row_num <= 3
);

Question 11: Delete Duplicate Emails

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.

After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.

Answer:

Solution #1:

DELETE FROM Person
WHERE id NOT IN (
    SELECT MIN(id)
    FROM Person
    GROUP BY email
);

Solution #2:

DELETE p1 FROM Person p1, Person p2 
WHERE p1.Id > p2.Id AND p1.Email = p2.Email;

Question 12: Rising Temperature

Write an SQL query to find all dates’ Id with higher temperatures compared to its previous dates (yesterday).

Answer:

Solution #1. Using a self-join:

SELECT today.id
FROM Weather today
JOIN Weather yesterday ON today.recordDate = DATEADD(day, -1, yesterday.recordDate)
WHERE today.temperature > yesterday.temperature;

Solution #2. Using a window function (LAG() or LEAD() depending on the database):

SELECT id
FROM (
    SELECT id, temperature,
           LAG(temperature) OVER (ORDER BY recordDate) AS prev_temperature
           -- For databases that support LAG() function
           -- LEAD(temperature) OVER (ORDER BY date) AS next_temperature
           -- For databases that support LEAD() function
    FROM Weather
) AS temp_comparison
WHERE temperature > prev_temperature;

Question 13: Trips and Users

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.
Write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between “2013–10–01” and “2013–10–03”. Round Cancellation Rate to two decimal points.

Answer:

SELECT 
    t.Request_at AS Day, 
    ROUND(
        SUM(CASE WHEN t.Status != 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2
    ) AS "cancellation rate"
FROM 
    Trips AS t
JOIN 
    Users AS uc ON t.Client_Id = uc.Users_Id AND uc.Banned = 'No'
JOIN 
    Users AS ud ON t.Driver_Id = ud.Users_Id AND ud.Banned = 'No'
WHERE 
    t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 
    t.Request_at;

Question 14: Find Customer Referee

Write an SQL query to report the names of the customer that are not referred by the customer with id = 2.

Answer:

SELECT name
FROM customer
WHERE referee_id <> 2 OR referee_id IS NULL;

Question 15: Customer Placing the Largest Number of Orders

Write an SQL query to find the customer_number for the customer who has placed the largest number of orders.
The test cases are generated so that exactly one customer will have placed more orders than any other customer.

Answer:

SELECT customer_number
FROM (
    SELECT customer_number, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_number
    ORDER BY order_count DESC
    LIMIT 1
) AS max_orders_customer;

Question 16: Big Countries

A country is big if:

• it has an area of at least three million (i.e., 3000000 km2) or
• it has a population of at least twenty-five million (i.e., 25000000).

Write an SQL query to report the name, population, and area of the big countries.

Answer:

SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;

Question 17: Classes More Than 5 Students

Write an SQL query to report all the classes that have at least five students.

Answer:

SELECT class
FROM enrollments
GROUP BY class
HAVING COUNT(student) >= 5;

Question 18: Human Traffic of Stadium

Write an SQL query to display the records with three or more rows with consecutive id’s, and the number of people is greater than or equal to 100 for each.

Return the result table ordered by visit_date in ascending order.

Answer:

WITH q1 AS (
    SELECT *, id - ROW_NUMBER() OVER(ORDER BY visit_date) AS id_diff
    FROM stadium
    WHERE people > 99
)
SELECT id, visit_date, people
FROM q1
WHERE id_diff IN (
        SELECT id_diff 
        FROM q1 
        GROUP BY id_diff 
        HAVING COUNT(*) > 2
    )
ORDER BY visit_date;

Question 19: Sales Person

Write an SQL query to report the names of all the salespersons who did not have any orders related to the company with the name “RED”.

Answer:

SELECT s.name
FROM
    SalesPerson AS s
    LEFT JOIN Orders AS o ON o.sales_id = s.sales_id
    LEFT JOIN Company AS c ON c.com_id = o.com_id
GROUP BY s.sales_id
HAVING IFNULL(SUM(c.name = 'RED'), 0) = 0;

Question 20: Tree Node

Each node in the tree can be one of three types:

• Leaf: if the node is a leaf node.
• Root: if the node is the root of the tree.
• Inner: If the node is neither a leaf node nor a root node.

Write an SQL query to report the type of each node in the tree.

Answer:

SELECT 
    id,
    CASE 
        WHEN p_id IS NULL THEN 'Root'
        WHEN p_id IN (SELECT id FROM Tree) and id IN (SELECT p_id from tree) THEN 'Inner'
        ELSE 'Leaf'
    END AS type
FROM Tree;

Question 21: Not Boring Movies

Write an SQL query to report the movies with an odd-numbered ID and a description that is not “boring”.

Answer:

SELECT *
FROM Cinema
WHERE id % 2 = 1
AND description != 'boring'
Order By rating Desc;

Question 22: Exchange Seats

Write an SQL query to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Answer:

SELECT ( CASE
            WHEN id%2 != 0 AND id != counts THEN id+1
            WHEN id%2 != 0 AND id = counts THEN id
            ELSE id-1
        END) AS id, student
FROM seat, (select count(*) as counts from seat) 
AS seat_counts
ORDER BY id ASC;

Question 23: Swap Salary

Write an SQL query to swap all ‘f’ and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.

update Salary 
    set sex = (case when sex = 'm' 
               then 'f' 
               else 'm'
               end)

Question 24: Actors and Directors Who Cooperated At Least Three Times

Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times.

Answer:

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3

Question 25: Sales Analysis III

Write an SQL query that reports the products that were only sold in the first quarter of 2019. That is, between 2019–01–01 and 2019–03–31 inclusive.

Answer:

Solution #1: Using the between , NOT IN operator.

SELECT DISTINCT s.product_id, p.product_name
FROM sales s
JOIN product p ON s.product_id = p.product_id
WHERE 
    s.sale_date BETWEEN '2019-01-01' AND '2019-03-31'
    AND s.product_id IN (
        SELECT product_id 
        FROM sales 
        WHERE sale_date BETWEEN '2019-01-01' AND '2019-03-31'
    );

Solution #2: group by () with having clause along with the use of min() and max() function.

SELECT s.product_id, p.product_name
FROM sales s
JOIN product p ON s.product_id = p.product_id
WHERE 
    s.sale_date >= '2019-01-01' 
    AND s.sale_date <= '2019-03-31'
GROUP BY 
    s.product_id, 
    p.product_name;

Question 26: Game Play Analysis I

Write an SQL query to report the first login date for each player.

Answer:

SELECT player_id, MIN(event_date) AS first_login
FROM Activity 
GROUP BY player_id;

Question 27: User Activity for the Past 30 Days I

Write an SQL query to find the daily active user count for a period of 30 days ending 2019–07–27 inclusively. A user was active on someday if they made at least one activity on that day.

Answer:

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users 
FROM Activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date;

Question 28: Article Views I

Write an SQL query to find all the authors that viewed at least one of their own articles.

Answer:

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id ASC;

Question 29: Market Analysis I

Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.

Answer:

SELECT 
    u.user_id AS buyer_id, 
    u.join_date, 
    COALESCE(t.orders_in_2019, 0) AS orders_in_2019 
FROM users u 
LEFT JOIN 
    (SELECT 
         buyer_id AS user_id, 
         COUNT(buyer_id) AS orders_in_2019 
     FROM orders 
     WHERE YEAR(order_date) = 2019 
     GROUP BY buyer_id) t 
ON 
    u.user_id = t.user_id;

Question 30: Reformat Department Table

Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.

Answer:

select id, 
 sum(case when month = 'jan' then revenue else null end) as Jan_Revenue,
 sum(case when month = 'feb' then revenue else null end) as Feb_Revenue,
 sum(case when month = 'mar' then revenue else null end) as Mar_Revenue,
 sum(case when month = 'apr' then revenue else null end) as Apr_Revenue,
 sum(case when month = 'may' then revenue else null end) as May_Revenue,
 sum(case when month = 'jun' then revenue else null end) as Jun_Revenue,
 sum(case when month = 'jul' then revenue else null end) as Jul_Revenue,
 sum(case when month = 'aug' then revenue else null end) as Aug_Revenue,
 sum(case when month = 'sep' then revenue else null end) as Sep_Revenue,
 sum(case when month = 'oct' then revenue else null end) as Oct_Revenue,
 sum(case when month = 'nov' then revenue else null end) as Nov_Revenue,
 sum(case when month = 'dec' then revenue else null end) as Dec_Revenue
from department
group by id
order by id;