This repository covers supplementary analysis of SQL for LeetCode and classic interview questions, tradeoff between performance optimization and developmental efficiency, and how it relates to general database design consideration (e.g. indexing and join). Specific sample databases are provided to illustrate tricky interview questions.
Only high-quality problems are selected. Pathological problems such as Find Median Given Frequency of Numbers are not discussed. Entry-level syntax problems such as Combine Two Tables are not discussed.
| # | Problems | Solutions | Level | Concept |
|---|---|---|---|---|
| 262 | Trips and Users | MySQL | Hard | Three-way join; filtering |
| 185 | Department Top Three Salaries | MySQL, MS SQL | Hard | Non-equijoin; aggregation; window functionsample |
| 579 | Cumulative Salary of Employee | MySQL, MS SQL | Hard | Self-join; left join; aggregation |
| 601 | Human Traffic of Stadium | MySQL, MS SQL | Hard | Self-join; de-duplication; window |
| 615 | Average Salary | MySQL | Hard | Case; aggregation, join |
| 618 | Students Report By Geography | MySQL, MS SQL | Hard | Full join, pivoting |
Other undiscussed problems have solutions lumped here.
This section covers commonly tested concepts during interviews. Many notebooks are inspired by problems people who violated their confidentiality agreement and posted interview masterials on Quora, Glassdoor, and 1point3acres. Data are either synthetic or from my personal challenge project.
| # | Problems | Solutions | Concept |
|---|---|---|---|
| 1 | Facebook Advertiser Status | MySQL | Transition diagram; conditional update |
| 2 | Spotify Listening History | MySQL | Update cumulative sum |
| 3 | Monthly Active User | MySQL | Functional dependency; aggregation; filtering |
| 4 | Page Recommendation | MySQL | Undirected edge; aggregation; existance |
| 5 | Pivoting Numeric Data | MySQL | Pivoting numeric data with case statement |
| 6 | Pivoting Text Data | MySQL | Pivoting text data with self-join |
| 7 | Un-pivoting Tables | MySQL | Un-pivoting tables using cross-join |
| 8 | Group by Bins | MySQL | Create custom column to group by |
| 9 | Consecutive Active Users | MySQL | Self-join, LAG() |
| 10 | Recommend Friends | MySQL | Self-join, de-duplication, aggregation |
| 11 | Spotify Similar Friends | MySQL | Three-way join, de-duplication, aggregation |
| 12 | Invalid Search | MySQL | NULL handling, rate calculation |
| 13 | Text Confirmation | MySQL | Rate calculation |
| 14 | Facebook Common Friends | MySQL | self join, three-way join |
| 15 | Facebook Recommend Friend | MySQL | Self join, four-way join |
| 16 | Instagram Common Follower | MySQL | Self join, Directed edge |
This section covers esoteric details of SQL language and use cases that may be completely useless in interview. Nevertheless, they come handy when judgement calls and some are simply fun to explore.
| # | Concept | Notebook |
|---|---|---|
| 1 | Random Sampling from Groups | MySQL8 |
| 2 | NULL Pathological Study | MySQL8 |
| 3 | Full Join | MySQL8 |
| 4 | Dynamic Query (Python) | MySQL8 |
| 5 | Stored Procedure | MySQL8 |
| 6 | Hacking Aggregation | MySQL8 |
| 6 | Multi Column Partition | MySQL8 |
This section WILL discusses common pitfalls such as nested selects, redundant temporary tables, unnecessary cross join, unnecessary hashset using distinct key word.
| # | Anti-patterns | Notebook |
|---|---|---|
| 1 | Ambiguous Group | MySQL8 |
| 2 | Bad Subquery | MySQL8 |
| 3 | Fail to Use Index | MySQL8 |