Taylor Brownlow

Cite

1. https://towardsdatascience.com/take-your-sql-from-good-to-great-part-1-3ae61539e92a
2. https://towardsdatascience.com/take-your-sql-from-good-to-great-part-2-cb03b1b7981b
3. https://towardsdatascience.com/take-your-sql-from-good-to-great-part-3-687d797d1ede
4. https://towardsdatascience.com/take-your-sql-from-good-to-great-part-4-99a55fd0e7ff
5. https://towardsdatascience.com/take-your-sql-from-good-to-great-part-5-7c996dbcc527

Common Table Expressions (CTEs)

Temporary tables that are available in a single query

WITH cte_name AS 
  (SELECT ... FROM ...)
SELECT * FROM cte_name

• Subquery vs CTE
  • Easier to understand
  • Faster iteration
  • Validations

Date and Time

• Construct
• Extract
• Add/Subtraction
• Truncate
• Parse
• Format
• Generate

Execution Order

1. FROM, JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

Join

• INNER JOIN
  • X and Y
  • Exclude NULL
• Range JOIN
  • Joining by inequalities, e.g. date ranges
• Lateral JOIN
  • Access columns after FROM
  • A FOR EACH operator

Window Functions

FUNC(xxx) OVER (PARTITION BY xxx ORDER BY xxx FUNC)