cookies-icon

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy.

Course

Advanced

Window Functions in PostgreSQL

A window function is an advanced SQL concept that enables you to maximize efficiency and minimize the complexity of queries that analyze partitions (windows), subgroups or sections of a data set. In this online course, you'll learn how to build complex aggregations with PostgreSQL window functions: OVER, RANK, PARTITION BY. Note: This is the only online course on PostgreSQL window functions you can find on the Internet.

Table of contents

Lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

limited to this course only

$29

Buy now

Want a better deal?

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

Get unlimited lifetime access to all 62 present and future courses

Save $420

Unlimited lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

to all 62 present and future courses

$ 179

$ 599

70% OFF

Buy now Full pricing
218

Interactive exercises

20 h

Estimated time

739

Users enrolled

☆☆☆☆☆
★★★★★

32 ratings

Description Table of contents Reviews Start learning

This online course will be of interest to database analysts, students, developers, and more. The prerequisite for the course is knowing the basics of SQL. Scroll down for more details.

Window functions (also known as analytic functions or OVER functions) are a very useful tool, especially when it comes to data aggregation. PostgreSQL's documentation describes the concept of window functions rather well:

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, the use of a window function does not cause rows to become grouped into a single output row – the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

These functions were introduced into the standard relatively recently, and that's why there are not many online courses that cover this concept. Fortunately, this course does!

This online course covers the syntax and semantics of PostgreSQL window functions and showcases how tremendously powerful they are. After you complete the course, you'll know the typical use cases of window functions, how to use OVER, ORDER BY and PARTITION BY to structure a frame, and the difference between ROWS and RANGE clauses.

RANGE OVER PostgreSQL window functions and crack complex aggregations today!

What's in It for Me?

  • 218 interactive exercises. Learn at your own pace, from anywhere and anytime. Interact with hands-on exercises for improved retention.
  • Lifetime access to the course. When you purchase the course, you'll get instant personal access to all of its content.
  • Online certification. Complete all of the exercises successfully and you'll get a certificate. You can publish the certificate on your LinkedIn profile (here's how.)
  • 30-day money-back guarantee. If you're not satisfied with the quality of the course, you can get a refund within 30 days of your purchase.
  • Hints for the exercises. You can make use of ready-made hints for exercises, or ask questions and share insights with other members of our community through the Discuss tab. You can also drop us a line at academy@vertabelo.com, and we'll be more than happy to answer!

What Are the Requirements?

  • Web browser
  • Internet connection
  • Knowledge of basic SQL provided in our SQL Basics in PostgreSQL course (or equivalent)

Learn How to:

  • Define the function window with PostgreSQL PARTITION BY.
  • RANK rows with window functions.
  • Create sophisticated window frames for your window functions.
  • Use the most essential analytic functions: LEAD, LAG, VALUE.
  • Create advanced statistics computed independently for various groups of rows.
  • Master all the skills you acquired in our extensive practice section.
  • Deeply understand PostgreSQL aggregate functions.

Who Should Take This Course?

  • PostgreSQL users who want to advance their querying skills
  • Developers who want to refresh what they already know
  • Beginner database analysts working with PostgreSQL
  • Students taking classes in relational databases
  • Anyone who wants to improve the use of PostgreSQL window functions

Table of contents

Free

Paid content

0%

Course progress

0/218

Exercises completed

1.

Introduction

Window functions? We'll explain what it's all about.

0/8
Start now

Introduction

Welcome to our course
What will I learn?
Table dog
Quiz – Question 1
Quiz – Question 2
Quiz – Question 3
Quiz – Question 4
Summary

2.

OVER()

Your first encounter with window functions.

0/17
Start now

Get to know tables

Introduction
Table department
Table purchase

OVER()

Introducing window functions
OVER() – first example
OVER() - practice

Computations with OVER()

Computations with OVER()
Computations with OVER() - exercise 2

Aggregate functions with OVER()

OVER() and COUNT()
Practice 1
Practice 2

OVER() and WHERE

Range of OVER()
OVER and WHERE

Summary

Summary
Exercise 1
Exercise 2
Congratulations

3.

OVER(PARTITION BY)

Discover how you can define the function window with PARTITION BY

0/16

Introduction

Introduction
Table route
Table journey
Table ticket

PARTITION BY

PARTITION BY – Introduction
PARTITION BY – first exercise
Range of OVER(PARTITION BY)
PARTITION BY MULTIPLE COLUMNS
OVER(PARTITION BY) – practice 1
OVER(PARTITION BY) – practice 2
OVER(PARTITION BY) – practice 3

Summary

Summary
Quiz table
Exercise 1
Exercise 2
Congratulations

4.

Ranking Functions

Learn how you can rank rows with window functions.

0/26

Introduction

Introduction
Table purchase

Ranking functions

Ranking functions - introduction
Function RANK()
RANK() – exercise
DENSE_RANK()
DENSE_RANK() – practice
ROW_NUMBER()
ROW_NUMBER() – practice
RANK(), DENSE_RANK(), ROW_NUMBER()
RANK() OVER(ORDER BY ... DESC)
RANK() with ORDER BY many columns
Ranking and ORDER BY
ROW NUMBER with ORDER BY
NTILE(X)
NTILE(X) – practice

Selecting n-th row

Explanation
Create the ranking
The full query
Practice 1
Practice 2

Summary

Summary
Table application
Exercise 1
Exercise 2
Congratulations

5.

Window Frame

Get to know how to create sophisticated window frames for your window functions.

0/26

Introduction

Introduction
Table stock_change
Table single_order
Table order_position

ROWS

General syntax
Window frame definition
First exercise
Rows – exercise 1
Rows – exercise 2
Rows – exercise 3
Abbreviations
Abbreviations – exercise

RANGE

RANGE explained
ROWS and RANGE – explanation
Boundaries with RANGE
Range – exercise 1
Range – exercise 2
Range – exercise 3

Default window frame

Default window frame – without ORDER BY
Default window frame – with ORDER BY

Summary

Summary
Table revenue
Exercise 1
Exercise 2
Exercise 3
Congratulations

6.

Analytics functions

Learn the most essential analytic functions.

0/30

Introduction

Introduction
Table website
Table statistics

LEAD and LAG

General syntax
Function LEAD(x)
LEAD(x) – practice
Deltas
Function LEAD(x, y)
Function LEAD(x, y, z)
Function LAG(x)
Function LAG(x, y)
Function LAG(x, y, z)
Practice 1
Practice 2

FIRST_VALUE, LAST_VALUE, NTH_VALUE

FIRST_VALUE(x)
FIRST_VALUE(x) – exercise
LAST_VALUE(x)
LAST_VALUE(x) with window frame
LAST_VALUE(x) – practice
LAST_VALUE(x) with different sorting
NTH_VALUE(x, n)
Practice 1
Practice 2
Practice 3

Summary

Summary
Table advertisement
Exercise 1
Exercise 2
Exercise 3
Congratulations

7.

PARTITION BY ORDER BY

Create advanced statistics computed independently for various groups of rows.

0/25

Introduction

Introduction
Table store
Table sales

Quick refresher

Scope of this part
PARTITION BY – refresher 1
PARTITION BY – refresher 2

PARTITION BY ORDER BY with ranking

RANK() with PARTITION BY ORDER BY
NTILE(x) with PARTITION BY ORDER BY
PARTITION BY ORDER BY in CTE
Practice 1
Practice 2
Practice 3

PARTITION BY ORDER BY with window frames

Explanation
Practice 1
Practice 2

PARTITION BY ORDER BY with analytical functions

LEAD() with PARTITION BY ORDER BY
FIRST_VALUE() with PARTITION BY ORDER BY
Practice 1
Practice 2

Summary

Summary
Table repairs
Exercise 1
Exercise 2
Exercise 3
Congratulations

8.

Window functions - evaluation order

Do you know when window functions are evaluated in a PostgreSQL query? Let's find out.

0/23

When window functions are evaluated

Introduction
Get to know the table – auction
Query evaluation order – problems with WHERE
Subqueries for problems with WHERE
Query evaluation order – problems with HAVING
Subqueries for problems with HAVING
Problems with GROUP BY
Subqueries for problems with GROUP BY
Window function in ORDER BY

Window functions and GROUP BY

What window functions see
What window functions see – continued
What window functions see – exercise
What window functions see – another exercise
Ranking by an aggregate
Ranking by an aggregate – practice
Day-to-day deltas with GROUP BY
Grouped rows, window functions and PARTITION BY

Summary

Summary
Table book
Exercise 1
Exercise 2
Exercise 3
Congratulations

9.

Practice field

Master all the skills you acquired so far in our big practice part.

0/32

Introduction

Welcome to Practice Field
Table customer
Table movie
Table review
Table single_rental
Table subscription
Table giftcard

Simple OVER()

OVER() – Summary
OVER() – Exercise 2
OVER() – Exercise 3

PARTITION BY

PARTITION BY – Summary
PARTITION BY – Exercise 2
PARTITION BY – Exercise 3
PARTITION BY – Exercise 4

Ranking functions

Ranking functions – Summary
Ranking functions – Exercise 2
Ranking functions – Exercise 3
Ranking functions – Exercise 4

Window frame

Window frame – Summary
Window frame – Exercise 2
Window frame – Exercise 3
Window frame – Exercise 4

Analytic functions

Analytic functions – Summary
Analytic functions – Exercise 2
Analytic functions – Exercise 3
Analytic functions – Exercise 4

ORDER BY PARTITION BY

PARTITION BY ORDER BY – Summary
PARTITION BY ORDER BY – Exercise 1
PARTITION BY ORDER BY – Exercise 2

Order of evaluation

Ranking of platforms
Grouping by ntiles

Summary

Congratulations

10.

Final Quiz

Test the skills you acquired in the whole course with this final quiz.

0/15

Introduction

Welcome to the Final Quiz
Table doctor
Table procedure

Final quiz

Question 1
Question 2
Question 3
Question 4
Question 5
Question 6
Question 7
Question 8
Question 9
Question 10
Question 11

Summary

Congratulations

Reviews (25)

Average rating

4.78 / 5

☆☆☆☆☆
★★★★★

32 ratings

Details

5 stars

85%

4 stars

9%

3 stars

6%

2 stars

0%

1 stars

0%