Top 10 problems to practice almost all SQL concepts
Covers all SQL concepts of JOIN, Aggregates, Window functions, and Subqueries
Introduction
I recently completed all 117 questions of SQL in 25 days on Leetcode which is probably one of the most popular websites to practice your coding skills in various programming languages. The website beautifully categorizes all the questions in three categories namely Easy, Medium, and Hard where the level of difficulty handsomely rises with each subsequent level. After completing all of them, I decided to highlight 10 questions which covers almost all the concepts ranging from Basic to Advanced SQL that you can practice in order to brush up your SQL programming skills. Additionally, all of these questions have been asked in interviews from almost all the big tech companies.
The following is the breakdown of SQL skills tested in every question:
- Q1 Average Salary: CTE, Aggregates in Window functions, CASE WHEN, Date functions such as DATE_PART, INNER JOIN
- Q2 Find Quiet students in results— Subqueries, MIN, MAX, Window functions, Window Alias, INNER JOIN, ALL keyword
- Q3 Human Traffic of Stadium — LEFT JOIN with Subqueries, CTE, ROW_NUMBER
- Q4 Number of Transactions per Visit —RECURSIVE CTE, COALESCE, COUNT
- Q5 Report contiguous dates (MySQL)— Date_sub, ROW_NUMBER
- Q6 Sales by Day of the week — Pivot table, CASE WHEN
- Q7 Department Top 3 Salaries— DENSE_RANK
- Q8 Restaurant Growth — PRECEDING for moving average, OFFSET
- Q9 Shortest distance in a Plane — CROSS JOIN, SQRT, POW
- Q10 Consecutive Numbers —LAG, LEAD
So, let’s get to the business!
- Given two tables below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.
Solution 1:
2. Write an SQL query to report the students (student_id, student_name) being “quiet” in ALL exams. A “quite” student is the one who took at least one exam and didn’t score neither the high score nor the low score.
Solution 2:
3. Write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
Solution 3:
4. Write an SQL query to find how many users visited the bank and didn’t do any transactions, how many visited the bank and did one transaction and so on.
Solution 4:
5. Write an SQL query to generate a report of period_state for each continuous interval of days in the period from 2019–01–01 to 2019–12–31.
Solution 5:
6. Write an SQL query to report how many units in each category have been ordered on each day of the week.
Solution 6:
7. Write an SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).
Solution 7:
8. Write an SQL query to compute moving average of how much customer paid in a 7 days window (current day + 6 days before) .
Solution 8:
9. Write a query to find the shortest distance between these points rounded to 2 decimals.
Solution 9:
10. Write an SQL query to find all numbers that appear at least three times consecutively.
Solution 10:
That’s a wrap! I hope you liked the questions and were able to practice some of the most important concepts of SQL. If you want to practice more questions like these, feel free to go on to my Github page where I have uploaded all the 117 solutions.
Thank you!
If you like my work, please follow me on Medium for reading more articles in near future.
- Read my other articles on Feature Engineering & Automating basic data analysis.
- Would love to connect with you on LinkedIn.
