After doing these courses, I feel confident creating professional visualizations and dashboards
Shoebur RahmanProcurement Specialist, Giz Bangladesh
Course Description
Have you ever wondered how data professionals use SQL to solve real-world business problems, like generating rankings, calculating moving averages and running totals, deduplicating data, or performing time intelligence? If you already know how to select, filter, order, join and group data with SQL, this course is your next step. By the end, you will be writing queries like a pro! You will learn how to create queries for analytics and data engineering with window functions, the SQL secret weapon! Using flights data, you will discover how simple it is to use window functions, and how flexible and efficient they are.The videos contain live transcripts you can reveal by clicking "Show transcript" at the bottom left of the videos.
The course glossary can be found on the right in the resources section.
To obtain CPE credits you need to complete the course and reach a score of 70% on the qualified assessment. You can navigate to the assessment by clicking on the CPE credits callout on the right.
Feels like what you want to learn?
Start Course for FreeWhat you'll learn
- Assess the use of aggregate window functions with frame clauses to compute running totals, moving averages, and other cumulative statistics
- Differentiate among core window functions such as ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTILE based on their operational behavior and analytic applications
- Distinguish between ROWS and RANGE frame types and Evaluate their effects on result sets when ordering columns contain duplicate values
- Identify the essential syntax elements of PostgreSQL window functions, including the OVER clause, ORDER BY, PARTITION BY, and frame definitions
- Recognize advanced data-reshaping and summarization techniques using CROSSTAB pivoting, ROLLUP, and CUBE to generate multi-level totals in PostgreSQL analyses.
Prerequisites
Curriculum
Course outline
1
Introduction to window functions
In this chapter, you'll learn what window functions are, and the two basic window function subclauses, ORDER BY and PARTITION BY.
- Introduction50 XP
- Window functions vs GROUP BY50 XP
- Numbering rows100 XP
- Numbering Olympic games in ascending order100 XP
- ORDER BY50 XP
- Numbering Olympic games in descending order100 XP
- Numbering Olympic athletes by medals earned100 XP
- Reigning weightlifting champions100 XP
- PARTITION BY50 XP
- Reigning champions by gender100 XP
- Reigning champions by gender and event100 XP
- Row numbers with partitioning50 XP
2
Fetching, ranking, and paging
In this chapter, you'll learn three practical applications of window functions: fetching values from different parts of the table, ranking rows according to their values, and binning rows into different tables.
3
Aggregate window functions and frames
In this chapter, you'll learn how to use aggregate functions you're familiar with, like `AVG()` and `SUM()`, as window functions, as well as how to define frames to change a window function's output.
4
Beyond window functions
In this last chapter, you'll learn some techniques and functions that are useful when used together with window functions.
S
PostgreSQL Summary Stats and Window Functions
Course
Complete
