
It’s almost the end of the year! I hope you all are studying hard and doing well. The beginning of the year (January and February) is usually the best time of the year to look for a job. So we need to be better prepared now and hit the ground running in 2021!
Tech companies usually have a large amount of data stored in their relational databases, so they want to see whether the candidates can extract and manipulate data using complex Sql queries before jumping into any modeling. SQL questions can be very tricky, so don’t underestimate SQL. Besides, no matter how good you are with SQL, you might still find it challenging to write the queries fast enough, especially under pressure. We all know how intimidating interviews can be, it’s ok if you didn’t fail the last interview. You can’t change what’s done, but you can learn from your mistakes and avoid making the same mistakes in the future.
Like all programming problems you’ll find in interviews, if you can’t spot the tricks, even easy problems on Leetcode can be very challenging. If you are new to SQL, you probably hope someone could share all the tricks with you. In this article, I will share all the common patterns I’ve seen from practicing SQL problems on Leetcode to help you be better at SQL immediately.
Prerequisite
The goal of this blog is not to go through all the SQL basics. Before we get started, it will be helpful if you are already familiar with SQL basic syntax, different joins, union, window functions, and CTE (Common Table Expressions.)
Here are some resources if you want to learn more about SQL:
SQL Concepts You Should Master
Here are some common concepts you will be asked during a SQL technical interview.
- Group By…Having Aggregate Functions
- Having >=All()
- CASE WHEN
- Finding the First/Last
- DATEDIFF and Self Join
- WHERE (X,Y) IN/NOT IN (SELECT X, Y FROM)
- Ranking
- Rolling Sum
- Like
- IFNULL()
In the following section, I will go over each concept with an example to help you better understand when to use these important concepts.
SQL CONCEPT #1: Group By…Having Aggregate Functions
Problem: Classes More Than 5 Students (Leetcode)
There is a table courses
with columns: student and class.
Please list out all classes which have more than or equal to 5 students.
Note: The students should not be counted duplicates in each course.
Solution
First, we need to count the student number in each class. And then select the ones that have more than 5 students. To get the number of students in each class, we use GROUP BY
and COUNT
. Note that we use DISTINCT
since the student name may be duplicated in a class as it is mentioned in the problem description. After that, we want to filter all the classes with more than or equal to 5 students. HAVING
is a very common clause in SQL queries. Like WHERE
, it helps filter data; however, HAVING
is used to specify the condition or conditions for a group or an aggregation.
SELECT
class, COUNT(DISTINCT student)
FROM
courses
GROUP BY class;
SQL CONCEPT #2 : Having >=All()
Problem: Sales Analysis I (Leetcode)
There are two tables: Product
with columns: product_id, product_name, and unit_price; and Sales
with columns: seller_id, product_id, buyer_id, sale_date, quantity, and price.
Write an SQL query that reports the best-seller by total sales price. If there is a tie, report them all.

Solution
We can first get the total price for each seller by using SUM
and GROUP BY
. After that, we can then use Having >=all()
concept to find the seller(s) that has/have the highest total sales price.
SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price) >= All(SELECT SUM(price) FROM Sales GROUP BY seller_id);
SQL CONCEPT #3: CASE WHEN
Problem: Reformat Department Table (Leetcode)
There is a table Department
with columns: id, revenue and month.
Write an SQL query to reformat the table such that there are a department id column and a revenue column for each month.

Solution
A CASE WHEN
adds a new column to the result of the query, notice that it always ends with END
. (CASE WHEN ... THEN ... ELSE ... END
). If you’ve ever used any programming language, you could think of it as an if-then-else type of logic.
CASE WHEN
with aggregates is one of the most common interview concepts. In this example, we simply sum up all the revenue if the condition is true.
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;
SQL CONCEPT #4: Finding the first/last
Problem: Game Play Analysis I (Leetcode)
There is a table Activity
with columns: player_id, device_id, event_date and games_played.
Write an SQL query that reports the first login date for each player.

Solution
When it comes to select the first or latest of something, we can use MIN
to find the first. Similarly, we can use MAX
to find the last one.
SELECT
player_id, MIN(event_date) AS first_login
FROM
Activity
GROUP BY
player_id;
SQL CONCEPT #5: DATEDIFF and Self Join
Problem: Rising Temperature (Leetcode)
There is a table: Weather
with columns: id, recordDate, ** and temperatur**e.
Write an SQL query to find all dates’ ids with higher temperatures than their previous dates (yesterday).

Solution
Dealing with the time format requires us to be familiar with the queries that deal with time. We usually use time constraint after WHERE
, HAVING
,CASE WHEN
. Time constraint includes selecting a special time range between 2020–01–01 and 2020–12–31
, a specific year/month/day MONTH('2020–12–31')
and the difference between two date values in years, months, weeks, and so on,DATEDIFF( date_part , start_date , end_date)
. Notice that if you don’t specify the date_part, DATEDIFF(start_date , end_date)
will return the number of days between two date values.
In this example, we use self join to create a new w2.recordDate
(yesterday) column. Then filter out ids that have higher temperatures than yesterday.
Note:DATEDIFF('2007–12–31','2007–12–30')
will return 1, whileDATEDIFF('2010–12–30','2010–12–31')
will returns -1.
SELECT w1.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1
AND w1.Temperature > w2.Temperature;
SQL CONCEPT #6: WHERE (X,Y) IN/NOT IN (SELECT X, Y FROM)
Problem: Department Highest Salary (Leetcode)
There are two tables: Employee
with columns: Id, Name, Salary and DepartmentId; and Department
with columns: Id, ** and Nam**e.
Write a SQL query to find employees who have the highest salary in each of the departments.

Solution
We can use MAX
to find the highest salary in each DepartmentId
. Then we can join these two tables to get DepartmentName
, Employee
and Salary
of the ones having the highest salary in their departments.
SELECT D.Name AS Department,
E.Name AS Employee, Salary
FROM Employee E INNER JOIN Department D
ON E.DepartmentId=D.Id
WHERE (E.DepartmentId, E.Salary)
IN (SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId);
SQL CONCEPT #7: Ranking
Problem: Rank Scores (Leetcode)
There is a table: Scores
with columns: id, and Score.
Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

Solution
Dense_Rank
is one example of ranking window functions. Ranking can add an ordered number to each of your output rows. There are three functions: Row_number
, Rank
and Dense_rank
. Using row_number
gives a result that must always be unique. Each row is assigned a different value even if they are equal. RANK
skips the number of positions after records with the same rank number (such as 1, 1, 3, 4, 5). In contrast, the ranking RANK_DENSE
doesn’t skip records with the same rank number (such as 1, 1, 2, 3, 4).
In the sample output, there’s no skip record in the Rank
column. Therefore, using RANK_DENSE
is more appropriate here. Note that when we want to name something that is the same as the reserved name, we use ‘X.’
SELECT Score, Dense_Rank() OVER(ORDER BY Score DESC) as 'Rank'
FROM Scores;
SQL CONCEPT #8: Rolling Sum
Problem: Last Person to Fit in the Elevator (Leetcode)
There is a table: Queue
with columns: person_id, person_name, weight and turn.
The maximum weight the elevator can hold is 1000.
Write an SQL query to find the person_name of the last person who will fit in the elevator without exceeding the weight limit. It is guaranteed that the person who is first in the queue can fit in the elevator.

Solution
Rolling sum/average/count are typical window functions. You can simply combine the aggregate functions SUM()
or AVG()
or COUNT()
with the OVER()
clause to get the rolling result.
In this example, we first get the rolling total (sum of all the weights from before) and then filter the last person with less than 1000 total rolling sum. When we want to find the first/last record, besides using min
and max
from above, we can also use ORDER BY ASC/DESC LIMIT 1
.
SELECT
person_name
FROM
(SELECT
person_name,
SUM(weight) OVER(ORDER BY turn) as total
FROM Queue) T
WHERE total<=1000
ORDER BY total DESC
LIMIT 1
SQL CONCEPT #9: LIKE/NOT LIKE
Problem: Patients With a Condition (Leetcode)
There is a table: Patients
with columns: patient_id, patient_name, and conditions.
Write an SQL query to report the patient_id, patient_name all conditions of patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.

Solution
The LIKE
operator is used in a WHERE
clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
%
– The percent sign represents zero, one, or multiple characters_
– The underscore represents a single character

In this example, we know that Type I Diabetes always starts with DIAB1. So we can use where
to filter patients who have conditions that start with ‘DIAB1.’
SELECT
*
FROM Patients
WHERE conditions LIKE '%DIAB1%'
SQL CONCEPT #10: IFNULL()
Problem: Ads Performance (Leetcode)
There is a table: Ads
with columns: ad_id, user_id, and action.
A company is running Ads and wants to calculate the performance of each Ad.
Write an SQL query to find the click-through rate of each Ad. [Ad total clicks/(Ad total clicks + Ad total views)]

Solution
The IFNULL()
function returns a specified value if the expression is NULL. If the expression is NOT NULL, IFNULL(expression, alt_value)
, this function returns the expression.
In this example, we first define the click-through rate. If the click-through rate is null, return 0. Don’t forget to use ROUND
and * 100
to make the code more readable.
SELECT
ad_id,
ROUND(IFNULL(SUM(action = 'Clicked') /
(SUM(action = 'Clicked') + SUM(action = 'Viewed')) * 100, 0), 2) AS ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;
Conclusion
There you have it! Now you know all the common patterns and tricks I’ve learned after solving 100 Leetcode SQL questions…. Believe me, I’ve been there. There have been many times that I’ve found myself not being good enough. But let me tell you, one day you can do all the coding problems without checking the solutions as long as you keep practicing. Good luck and happy learning!
If you find this helpful, please follow me and check out my other blogs. ❤️
Discovering your Music Taste with Python and Spotify API
How to Convert Jupyter Notebooks into PDF
How to Prepare for Business Case Interview as an Analyst?
Understanding and Choosing the Right Probability Distributions with Examples
Building a Product Recommendation System for E-Commerce: Part I – Web Scraping