My Secret Recipe for SQL Query

摘要： Following is the recipe. and the secret sauce is understanding the SQL internal sequence, which is so important not many people talk about this.

▲Photo is from a triathlon race. Beginners struggle and zigzag in the open water, whereas the pros go straight to the target buoy. That’s the moral of this article. Give you insight of SQL so you can avoid zigzagging on you learning process. Photo is from the author.

After solving more than 150 leetcode SQL problems and using SQL in my daily job, I am convinced that the recipes below will be a ubiquitous template for most SQL interviews and daily work.

It helped me to pass 90% of leetcode problems; the rest of 10% are simple and could be solved without the recipes.

Following is the recipe. and the secret sauce is understanding the SQL internal sequence, which is so important not many people talk about this.

SQL is like a small programming language following sequence of commands.

The inputs for SQL are normally table/tables; SQL language will execute a sequence of queries. The tables will be joined, grouped, and ordered, window functions will be created additional information etc… The output of the SQL query is another table with information interesting to users.

The sequence is the execution order of SQL. Most frustrations of the beginners I coached are not aware or not understanding the order of SQL. If you call something that has not been created. There will be error messages.

There are two types of sequences within a single query and sequences between queries.

The sequence within a single Query: It is clearly documented in the following flow chart with examples. Learners are encouraged to use it as a cheatsheet. Another tip is when you write queries, write in the sequence as the list below, even though Select is normally the very beginning of your query. Starting with FROM, WHERE etc.

▲internal sequence of SQL query

Sequence of queries: or sequence of subquery. There are genius who can write few lines of code with nested, cross join, window function in it and it works. Since my brain functions with limited bandwidth as most people, I prefer structure to superman intelligence.

the cte (or common table expression) is like a logical chain that allows you to break big problems into smaller, easier steps. the enhanced tables can be used by subsequent cte queries and final queries. Therefore, you have many places to enrich, and enhance the information you want to extract, which is the main reason I prefer this template.

For example, the output subquery one can be used by subsequent cte 2, cte 3 and final query.

▲the sequence of CTEs. the cte output tables can be used by subsequent ctes and final queries. There are almost unlimited ways of information extraction and enrichment. That is why I use it as my secrete recipe for leetcode challenges and daily work

Tip: Notice that I write the wrapper sentence in separate lines. because I can easily comment on and off while debugging. and after intermedia tables make sense, I can easily uncomment so it become part of the main data pipeline

In leetcode challenges, it is rare for me to use more than 2 CTEs. Most medium to hard problems can be solved within one or two CTEs.

In one case, I would recommend not including the query in CTE. When the query returns a single value or list of values. The reason is that it returns a number or a list which you can use directly in SELECT or WHERE clauses. Following are two examples

轉貼自Source： medium.com

若喜歡本文，請關注我們的臉書 Please Like our Facebook Page： 　　　Big Data In Finance