Advanced Course

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.

4.83 ★★★★★ ★★★★★ (798 ratings)

15,151 learners enrolled

Lifetime access

limited to this course only

$39

Want a better deal?

Get unlimited lifetime access to all 72 present and future courses

Save $450

Unlimited lifetime access

to all 72 present and future courses

$149 $599

75% OFF

Free trial Yes

Certificate of completion Yes

Time to complete 20 h

Coding challenges 218

Skills you will gain

  • Understand the syntax of the OVER() clause
  • Learn how to combine OVER() and PARTITION BY
  • Discover how to combine OVER() and ORDER BY
  • Reveal how to rank rows using RANK, DENSE_RANK, and ROW_NUMBER
  • Learn how to create sophisticated window frames using ROWS and RANGE.
  • Understand the syntax of the analytic functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTILE.
  • Learn how to combine window functions and GROUP BY
  • Understand when to use window functions and when to use GROUP BY
  • Deeply understand PostgreSQL aggregate functions.
  • Discover how window functions in PostgreSQL can be used to build rankings
  • Compute running totals and running averages
  • Find the best and worst performers
  • Investigate trends across time
  • Calculate contributions to the whole, such as commission percentages

Last reviews

Table of contents

Course progress 0%

Exercises completed 0/218

  1. Introduction

    0/8

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

  2. OVER()

    0/17

    Your first encounter with window functions.

  3. OVER(PARTITION BY)

    0/16

    Discover how you can define the function window with PARTITION BY

  4. Ranking Functions

    0/26

    Learn how you can rank rows with window functions.

  5. Window Frame

    0/26

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

  6. Analytics functions

    0/30

    Learn the most essential analytic functions.

  7. PARTITION BY ORDER BY

    0/25

    Create advanced statistics computed independently for various groups of rows.

  8. Window functions - evaluation order

    0/23

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

  9. Practice field

    0/32

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

  10. Final Quiz

    0/15

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

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Description

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!

Cover image for the course 'Window Functions in PostgreSQL'

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.

What Are the Requirements?

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

What Am I Going to Get from This SQL Course?

In this PostgreSQL course you will learn:

  • The syntax of the OVER() clause
  • How to combine OVER() and PARTITION BY
  • How to combine OVER() and ORDER BY
  • How to rank rows using RANK, DENSE_RANK, and ROW_NUMBER
  • How to create sophisticated window frames using ROWS and RANGE.
  • The syntax of the analytic functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTILE.
  • How to combine window functions and GROUP BY
  • When to use window functions and when to use GROUP BY
  • Deeply understand PostgreSQL aggregate functions.

You'll discover how window functions in PostgreSQL can be used to:

  • Build rankings
  • Compute running totals and running averages
  • Find the best and worst performers
  • Investigate trends across time
  • Calculate contributions to the whole, such as commission percentages

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

Reviews (33)

Average rating

4.83/5

★★★★★ ★★★★★ (798 ratings)

5 stars

85%

4 stars

15%

3 stars

0%

2 stars

0%

1 stars

0%