After doing these courses, I feel confident creating professional visualizations and dashboards
Shoebur RahmanProcurement Specialist, Giz Bangladesh
Course Description
Unlock a slew of new problem-solving tools with the power of Snowflake window functions! In this course, you'll master the tools needed to solve problems like identify outliers in your data and calculate moving averages.First, you'll differentiate between traditional aggregation functions and window functions. You'll nail down the anatomy of a window function by assigning row numbers and rankings to all records in a Snowflake query. Once you've gotten your feet under you, you'll pair these window functions with partitions. This will give you the power to created ordered groups of records, and compare sequential values.You'll wrap up the course with aggregate window functions and rolling averages; two of the most handy applications of window functions for wrangling and analyzing data. When all is said and done, you'll have a whole new skillset that will supercharge your Snowflake queries!
Feels like what you want to learn?
Start Course for FreeWhat you'll learn
- Explain what window functions are and how they differ from traditional aggregation.
- Rank and order records within a dataset using ranking window functions.
- Partition data into meaningful groups to perform targeted comparisons and calculations.
- Compare values across sequential rows to identify trends and changes over time.
- Calculate running totals, cumulative metrics, and moving averages using aggregate window functions.
Prerequisites
Curriculum
Course outline
1
Window Functions
Open the window to a world of possibilities with Snowflake window functions! You'll get the ball rolling by differentiating window functions from traditional functions. Then, you'll learn how to provide a row number and ranking for each record in a query. Once you've nailed down the basics, you'll put the "window" in window functions, using PARTITION BY. You'll explore how to find and use the first and last value of a certain window before wrapping up with a sneak peek into aggregation functions.
- Assigning row numbers50 XP
- Building a window function100 XP
- Adding row numbers to charging sessions100 XP
- Riding the heat wave100 XP
- Ranking window functions50 XP
- Was that a fast charge?100 XP
- Comparing energy consumed100 XP
- Partitioning data in a window function50 XP
- Follow the money!100 XP
- Digging into charging costs100 XP
2
Ranking Window Functions
Time to crank it up! In this chapter, you’ll take ranking functions to the next level. You’ll start with a variant of RANK, called DENSE_RANK, which handles ties in a bit of a different way. You’ll also explore a more robust version of the functions you saw in the previous lesson using NTH_VALUE. Next, you’ll create “buckets” of data using NTILE, which is more useful than you may think. You’ll also pick up a nifty little tool called CUME_DIST to find the number of records less than or equal to a certain record in a window. You’ll wrap up the chapter with one of the most powerful applications of window functions you’ve seen so far; LAG and LEAD.
3
Aggregate Window Functions
You’ll start this final chapter with aggregation functions like AVG, COUNT, and SUM. You’ll compare the output of these functions to individual records in a window, as well as to perform additional calculations. After this, you’ll master the most exciting application of window functions; running and moving calculations! You’ll start by calculating running averages and totals for different metrics for electric vehicle charging. Finally, you’ll wrap up the course by generating moving totals and averages with a sliding window!
S
Window Functions in Snowflake
Course
Complete
