- window function only work in the SELECT statement
- use join or exist to filter the data based on another dateset
- join condition is not only for the column being match, but also filter the source table (1607. Sellers With No Sales)
- use ‘true’/‘false’ or 1/0 represent boolean
- when using CASE statement, WHEN statement have to be used in each conditional expression
- When joining same table multiple times, add number into alias like R1, R2, R3…Then you can select the values using R1.col, R2.col, R3.col…
- Pivoting is not supported by every database, cross join could be one of the solution
- You can filter the group by result where the aggregation condition in inside the group (1511. Customer Order Frequency)
- Don’t forget alias of sub-query after FROM
- Recursive CTE (1270. All People Report to the Given Manager) is not only useful for hierarchy data, it is also useful for row-generation
grouping sequential data by state
3 ways to exclude the set by groups (except, not exist and left join)