Who is this course for? People who have completed the foundation and intermediate courses and want to do more powerful analysis with SQL.
Learning Objectives: Attendees will learn to write SQL statements to retrieve data across several related tables, calculate new columns, aggregate data and calculate over groups of data e.g. rolling averages or cumulative totals.
Pre-requisites: A basic understanding of the SQL language – for example by attending our SQL Foundation course.
Course Content:
Tally tables. When to use and why they are useful.
SQL Window functions are powerful, flexible and applicable to many problems that would otherwise be hard to specify in SQL – for example, rolling totals, ranking, Top N per category, daily change… The details of Window functions:
- the OVER clause to define the window
- the PARTITION BY clause – apply the windows (and group/aggregate) over the distinct values in one or more columns
- the ORDER BY clause – example of use: e.g. ranking by category
- the special Window functions: ROW_NUMBER, LEAD and LAG, RANK and DENSE_RANK
Problems that can be solved with Window functions: running totals, ranking, Top N per category, and daily changes in balances.
Below are some images from the lab exercises on the course.
