In this demo, we'll be moving beyond the Big 6 clauses (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY) and introduce more advanced SQL querying topics.
Our goal will be to first generate a series of dates using a recursive CTE, combine it with a sales table using a LEFT JOIN, and finally fill in the missing NULL values using the COALESCE null function and the LAG & LEAD window functions.
Along the way, we'll be covering 12 advanced SQL concepts:
UNION
UNION ALL
Subqueries
LEFT JOIN
INNER JOIN
CTEs
Recursive CTEs
Date Expressions
CAST
COALESCE
ROUND
Window Functions
… all in less than 20 minutes!
Course Outline
Introduction
0:00
UNION vs UNION ALL
01:05
Suqueries
03:01
LEFT JOIN vs INNER JOIN
04:14
CTEs
06:44
Recursive CTEs
10:02
Date Expressions
11:16
CAST
12:16
COALESCE
14:28
ROUND
15:55
Window Functions (ROW_NUMBER, LAG & LEAD)
16:33
Final Query
19:35
Outro
21:09
Alice Zhao
Lead Data Science Instructor
Alice Zhao is a seasoned data scientist and author of the book, SQL Pocket Guide, 4th Edition (O'Reilly). She's an adjunct lecturer for Northwestern University's Machine Learning and Data Science program, where she teaches Python, SQL, R, data warehousing and data visualization.
Recommended
learning:
