Leetcode SQL 50 interview questions with solution: A Guide for Coders

Introduction

Hi coders,

In this blog, we will tackle SQL questions from LeetCode. If you're a coder, a computer science student, or simply interested in coding, you may be familiar with LeetCode. It's a platform where coding enthusiasts practice problems to enhance their critical thinking and problem-solving skills. Most of us practice programming questions on leetcode so here we practice SQL questions which is taken from leetcode.

We'll start with basic SQL questions focused on SELECT, joins, and aggregate functions. Once we've covered these foundational topics, we'll transition to more advanced concepts. Before diving into the questions, ensure you have a basic understanding of SQL syntax and statements, including SELECT, various joins (self join, left join, right join, outer join), and basic aggregate functions.

Here are some SQL interview questions with solution:

Q1 1757. Recyclable and Low Fat Products

SELECT product_id FROM Products WHERE low_fats = 'Y' AND recyclable = 'Y';

Explanation: This query retrieves the product_id of all products that are both low in fat (low_fats = 'Y') and recyclable (recyclable = 'Y').

Q2 584. Find Customer Referee

-- method 1

SELECT name FROM Customer WHERE COALESCE(referee_id, 0) <> 2;

-- method 2

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

-- method 3

SELECT name FROM Customer WHERE IFNULL(referee_id,0) <> 2;

-- method 4

SELECT name FROM Customer WHERE id NOT IN (SELECT id FROM Customer

WHERE referee_id = 2);

Explanation: 

Method 1: The COALESCE function replaces NULL values in the referee_id column with 0 and ensures that any referee with referee_id not equal to 2 is selected.

Method 2: This method checks for customers whose referee_id is not 2 or is NULL.

Method 3: This method is similar to the first one but uses IFNULL to replace NULL values with 0.

Method 4: This method finds all customer names whose id is not in the list of customers having referee_id = 2.

Q3 595. Big Countries

SELECT name, population, area FROM World WHERE area >= 3000000

OR population >= 25000000;

Explanation: This query selects the names of countries with an area greater than or equal to 3 million (3000000) square kilometers or a population greater than or equal to 25 million (25000000).

Q4 1148. Article Views I

SELECT DISTINCT author_id as id FROM Views WHERE author_id = viewer_id

ORDER BY ID ASC;

Explanation: This query retrieves unique author_id values where the author is also the viewer of their article, ordered by id in ascending order.

Q5 1683. Invalid Tweets

SELECT tweet_id FROM Tweets WHERE length(content) > 15;

Explanation: This query retrieves the tweet_id of tweets whose content length exceeds 15 characters.

Q6 1378. Replace Employee ID With The Unique Identifier

-- method 1

SELECT EU.unique_id, E.name FROM Employees AS E

LEFT JOIN EmployeeUNI AS EU ON E.id = EU.id;

-- method 2

SELECT EU.unique_id, E.name FROM Employees AS E

LEFT JOIN EmployeeUNI AS EU USING(id);

Explanation: 

Method 1: This query joins the Employees table with the EmployeeUNI table on the id column, returning the employee's name along with their unique_id.

Method 2: This is an alternative to the previous query, using the USING(id) syntax to simplify the join condition.

Q7 1068. Product Sales Analysis I

SELECT p.product_name, s.year, s.price FROM Sales AS s

 INNER JOIN Product AS p ON p.product_id = s.product_id;

Explanation: This query joins the Sales and Product tables on product_id to retrieve the product_name, year, and price for each sale.

Q8 1581. Customer Who Visited but Did Not Make Any Transactions

-- method 1

SELECT v.customer_id, COUNT(v.visit_id) AS count_no_trans FROM Visits AS v

LEFT JOIN Transactions AS t using(visit_id) WHERE transaction_id IS NULL

GROUP BY v.customer_id;

Explanation: This query counts how many times each customer visited without making any transactions by using a LEFT JOIN between the Visits and Transactions tables.

-- method 2

SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits

WHERE visit_id NOT IN (SELECT visit_id FROM Transactions)

GROUP BY customer_id;

Explanation: This method checks for visit_id values that are not present in the Transactions table and counts the number of visits for each customer.

-- method 3

SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits v

WHERE NOT EXISTS (SELECT visit_id FROM Transactions t

WHERE t.visit_id = v.visit_id) GROUP BY customer_id;

Explanation: This method uses the NOT EXISTS clause to count visits that have no corresponding transaction.

Q9 197. Rising Temperature

SELECT w1.Id FROM Weather w1, Weather w2

WHERE w1.temperature > w2.temperature AND DATEDIFF(w1.recordDate, w2.recordDate) = 1;

Explanation: This query compares temperatures recorded on consecutive days and selects the day where the temperature was higher than the previous day.

Q10 1661. Average Time of Process Per Machine

-- method 1: using self join

SELECT a1.machine_id, round(avg(a2.timestamp-a1.timestamp), 3) AS processing_time

FROM Activity a1 JOIN Activity a2

ON a1.machine_id=a2.machine_id AND a1.process_id=a2.process_id

AND a1.activity_type='start' AND a2.activity_type='end'

GROUP BY a1.machine_id;

Explanation: This method uses a self-join to calculate the average processing time by subtracting the start timestamp from the end timestamp.

-- method 2: using subquery

SELECT a.machine_id,

round(

    (SELECT avg(a1.timestamp) FROM Activity a1

WHERE a1.activity_type = 'end' AND a1.machine_id = a.machine_id) -

    (SELECT avg(a1.timestamp) FROM Activity a1

WHERE a1.activity_type = 'start' AND a1.machine_id = a.machine_id)

,3) AS processing_time FROM Activity a GROUP BY a.machine_id;

Explanation: This method uses subqueries to calculate the average timestamps for start and end activities and then subtracts them to get the processing time.

Q11 577. Employee Bonus

SELECT e.name, b.bonus FROM employee e LEFT JOIN bonus b USING(empId)

WHERE bonus < 1000 OR bonus IS NULL;

Explanation: This query retrieves the names of employees who either have no bonus (NULL) or a bonus less than 1000.

Q12 1280. Students and Examinations

SELECT s.student_id, s.student_name, sb.subject_name, COUNT(e.subject_name)

AS attended_exams FROM students s CROSS JOIN subjects sb

LEFT JOIN examinations e ON e.subject_name = sb.subject_name

AND e.student_id = s.student_id

GROUP BY s.student_id, s.student_name, sb.subject_name

ORDER BY s.student_id, sb.subject_name;

Explanation: This query retrieves each student's student_id, student_name, the subject_name, and the count of exams they have attended for each subject.

Q13 570. Managers with at Least 5 Direct Reports

-- method1: having clause

SELECT m.name FROM employee AS e INNER JOIN employee AS m ON e.managerId = m.id

GROUP BY e.managerId HAVING count(e.id) >= 5;

-- method2: subquery

SELECT name FROM employee WHERE id IN

(SELECT managerId FROM employee GROUP BY managerId HAVING count(managerId)>=5);

Explanation: 

Method 1: This method counts the number of direct reports each manager has, selecting managers with at least 5 direct reports.

Method 2: This method uses a subquery to find the managerId values that have 5 or more direct reports and retrieves the corresponding manager names.

Q14 1934. Confirmation Rate

SELECT s.user_id, round(avg(IF(action = 'confirmed',1,0)),2) AS confirmation_rate

FROM signups AS s LEFT JOIN confirmations AS c ON s.user_id = c.user_id GROUP BY user_id;

Explanation: This query calculates the confirmation rate for each user by averaging the action values in the confirmations table. If the action is 'confirmed', the value is 1, otherwise 0.

Conclusion

In this blog, we've walked through a variety of SQL questions from LeetCode, covering both basic and intermediate topics. By practicing queries that involve SELECT statements, joins, and aggregate functions, you're building a solid foundation in SQL. As you advance through more complex scenarios, your problem-solving skills and understanding of database operations will deepen.

These SQL questions are a great way to sharpen your skills, especially if you're preparing for coding interviews or want to improve your ability to handle real-world data challenges. Continue practicing, explore more advanced topics like subqueries and window functions, and you'll find yourself becoming more confident in writing efficient and optimized SQL queries.

Happy coding!