Study Plan Introduction
A numerous amount of Data Science positions require proficiency in SQL. As a result, a SQL technical interview is commonly included in the data science interview process.
LeetCode is a great source for practicing interviews, ranging from data structures & algorithms to SQL. LeetCode categorizes their questions into difficulty levels of easy, medium, and hard. LeetCode also has their own SQL study plans; however, the SQL topics are not sorted out well (or correctly in some cases) and therefore I find LeetCode more helpful as a testing tool than a studying tool.
This study guide categorizes SQL questions into different SQL topics so that the user can improve competency by area **** _via focus and repetition_s. You will find selected links to LeetCode questions that are a good representation of Interview Questions for each topic.
Study Plan Timeline
A good pace for this study guide is to attempt 2–4 Sql questions a day. This allows you to choose a SQL topic you will focus on for the study duration and solidify your understanding of that area. If you feel that you are already strong in the basic areas, you can skip them and focus on the more intermediate and advanced topics.
Day 9— Day 10: These days cover window functions. I’ve found that a good study source is sqltutorial.org SQL Window Functions page. Read through the short article, then go over each window function on the bottom of the page (value window functions and ranking window functions). There aren’t many non-premium LeetCode questions on window functions, so getting further practice in the area, especially ranking functions, is recommended.
Study Plan
Day 1: Select and Filter
The SQL SELECT function selects columns from one or more tables. The SQL WHERE clause lets you filter rows based on one or more conditions.
- Select when columns match
- Select with multiple conditions
- Filter on column with nulls
- Select second highest value
Day 2: Joins and Union
There are 4 main types of joins: INNER JOIN (or JOIN), LEFT JOIN / RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.
The SQL UNION function combines result sets of two or more select statements into a single result. The SQL UNION ALL function retains the duplicate rows.
Day 3: Group By
The SQL GROUP BY clause groups rows based on values of one or more columns, returning one row for each group. You can perform aggregate functions such as SUM and COUNT to each group.
Day 4: Group By
The SQL HAVING clause specifies a condition for groups defined in GROUP BY. This is typically used to filter rows resulted from group by and aggregation.
Day 5: Case When
The SQL CASE function evaluates one of more conditions and returns results defined by that condition. This is just like an if statement.
Day 6: Subquery
A SQL subquery is a query nested inside of another query. You can use the results of one query to support another.
Day 7: Update and Remove From Table
The SQL UPDATE function changes existing data in a table. The SQL DELETE function removes one or more rows from a table.
Day 8: String Processing
There are many string processing functions such as UPPER, LOWER, CONCAT, GROUP_CONCAT, TRIM, and utilization of regular expressions. Become familiar with some common SQL string functions here.
Day 9: Value Window Functions
The FIRST_VALUE() and LAST_VALUE() window functions return the first value and last value in an ordered set of values respectively. The LAG() window function provides access to data of the previous row or rows. The LEAD() window function provides access to data of the following row or rows.
Day 10: Ranking Window Functions
Notable ranking window functions are ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(). You can familiarize yourself with value and ranking window functions here.
Summary
To test SQL proficiency, SQL technical interviews are frequently included in the data science interview process. LeetCode serves as a great resource for practicing interviews, however, the randomization of questions generates a testing tool rather than a study tool. To align study focus to one SQL topic at a time, this study guide categorizes LeetCode SQL questions into core topics that come up in SQL interviews.
