Window Functions in PostgreSQL

Table of contents

1. 1. Introduction

   0/8

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

   List of exercises Start now
   1. Introduction
      1. Welcome to our course
      2. What will I learn?
      3. Table dog
      4. Quiz – Question 1
      5. Quiz – Question 2
      6. Quiz – Question 3
      7. Quiz – Question 4
      8. Summary

2. 2. OVER()

   0/17

   Your first encounter with window functions.

   List of exercises Start now
   1. Get to know tables
      1. Introduction
      2. Table department
      3. Table purchase
   2. OVER()
      1. Introducing window functions
      2. OVER() – first example
      3. OVER() - practice
   3. Computations with OVER()
      1. Computations with OVER()
      2. Computations with OVER() - exercise 2
   4. Aggregate functions with OVER()
      1. OVER() and COUNT()
      2. Practice 1
      3. Practice 2
   5. OVER() and WHERE
      1. Range of OVER()
      2. OVER and WHERE
   6. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Congratulations

3. 3. OVER(PARTITION BY)

   0/16

   Discover how you can define the function window with PARTITION BY

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table route
      3. Table journey
      4. Table ticket
   2. PARTITION BY
      1. PARTITION BY – Introduction
      2. PARTITION BY – first exercise
      3. Range of OVER(PARTITION BY)
      4. PARTITION BY MULTIPLE COLUMNS
      5. OVER(PARTITION BY) – practice 1
      6. OVER(PARTITION BY) – practice 2
      7. OVER(PARTITION BY) – practice 3
   3. Summary
      1. Summary
      2. Quiz table
      3. Exercise 1
      4. Exercise 2
      5. Congratulations

4. 4. Ranking Functions

   0/26

   Learn how you can rank rows with window functions.

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table purchase
   2. Ranking functions
      1. Ranking functions - introduction
      2. Function RANK()
      3. RANK() – exercise
      4. DENSE_RANK()
      5. DENSE_RANK() – practice
      6. ROW_NUMBER()
      7. ROW_NUMBER() – practice
      8. RANK(), DENSE_RANK(), ROW_NUMBER()
      9. RANK() OVER(ORDER BY ... DESC)
      10. RANK() with ORDER BY many columns
      11. Ranking and ORDER BY
      12. ROW NUMBER with ORDER BY
      13. NTILE(X)
      14. NTILE(X) – practice
   3. Selecting n-th row
      1. Explanation
      2. Create the ranking
      3. The full query
      4. Practice 1
      5. Practice 2
   4. Summary
      1. Summary
      2. Table application
      3. Exercise 1
      4. Exercise 2
      5. Congratulations

5. 5. Window Frame

   0/26

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

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table stock_change
      3. Table single_order
      4. Table order_position
   2. ROWS
      1. General syntax
      2. Window frame definition
      3. First exercise
      4. Rows – exercise 1
      5. Rows – exercise 2
      6. Rows – exercise 3
      7. Abbreviations
      8. Abbreviations – exercise
   3. RANGE
      1. RANGE explained
      2. ROWS and RANGE – explanation
      3. Boundaries with RANGE
      4. Range – exercise 1
      5. Range – exercise 2
      6. Range – exercise 3
   4. Default window frame
      1. Default window frame – without ORDER BY
      2. Default window frame – with ORDER BY
   5. Summary
      1. Summary
      2. Table revenue
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Congratulations

6. 6. Analytics functions

   0/30

   Learn the most essential analytic functions.

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table website
      3. Table statistics
   2. LEAD and LAG
      1. General syntax
      2. Function LEAD(x)
      3. LEAD(x) – practice
      4. Deltas
      5. Function LEAD(x, y)
      6. Function LEAD(x, y, z)
      7. Function LAG(x)
      8. Function LAG(x, y)
      9. Function LAG(x, y, z)
      10. Practice 1
      11. Practice 2
   3. FIRST_VALUE, LAST_VALUE, NTH_VALUE
      1. FIRST_VALUE(x)
      2. FIRST_VALUE(x) – exercise
      3. LAST_VALUE(x)
      4. LAST_VALUE(x) with window frame
      5. LAST_VALUE(x) – practice
      6. LAST_VALUE(x) with different sorting
      7. NTH_VALUE(x, n)
      8. Practice 1
      9. Practice 2
      10. Practice 3
   4. Summary
      1. Summary
      2. Table advertisement
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Congratulations

7. 7. PARTITION BY ORDER BY

   0/25

   Create advanced statistics computed independently for various groups of rows.

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table store
      3. Table sales
   2. Quick refresher
      1. Scope of this part
      2. PARTITION BY – refresher 1
      3. PARTITION BY – refresher 2
   3. PARTITION BY ORDER BY with ranking
      1. RANK() with PARTITION BY ORDER BY
      2. NTILE(x) with PARTITION BY ORDER BY
      3. PARTITION BY ORDER BY in CTE
      4. Practice 1
      5. Practice 2
      6. Practice 3
   4. PARTITION BY ORDER BY with window frames
      1. Explanation
      2. Practice 1
      3. Practice 2
   5. PARTITION BY ORDER BY with analytical functions
      1. LEAD() with PARTITION BY ORDER BY
      2. FIRST_VALUE() with PARTITION BY ORDER BY
      3. Practice 1
      4. Practice 2
   6. Summary
      1. Summary
      2. Table repairs
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Congratulations

8. 8. Window functions - evaluation order

   0/23

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

   List of exercises Buy course
   1. When window functions are evaluated
      1. Introduction
      2. Get to know the table – auction
      3. Query evaluation order – problems with WHERE
      4. Subqueries for problems with WHERE
      5. Query evaluation order – problems with HAVING
      6. Subqueries for problems with HAVING
      7. Problems with GROUP BY
      8. Subqueries for problems with GROUP BY
      9. Window function in ORDER BY
   2. Window functions and GROUP BY
      1. What window functions see
      2. What window functions see – continued
      3. What window functions see – exercise
      4. What window functions see – another exercise
      5. Ranking by an aggregate
      6. Ranking by an aggregate – practice
      7. Day-to-day deltas with GROUP BY
      8. Grouped rows, window functions and PARTITION BY
   3. Summary
      1. Summary
      2. Table book
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Congratulations

9. 9. Practice field

   0/32

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

   List of exercises Buy course
   1. Introduction
      1. Welcome to Practice Field
      2. Table customer
      3. Table movie
      4. Table review
      5. Table single_rental
      6. Table subscription
      7. Table giftcard
   2. Simple OVER()
      1. OVER() – Summary
      2. OVER() – Exercise 2
      3. OVER() – Exercise 3
   3. PARTITION BY
      1. PARTITION BY – Summary
      2. PARTITION BY – Exercise 2
      3. PARTITION BY – Exercise 3
      4. PARTITION BY – Exercise 4
   4. Ranking functions
      1. Ranking functions – Summary
      2. Ranking functions – Exercise 2
      3. Ranking functions – Exercise 3
      4. Ranking functions – Exercise 4
   5. Window frame
      1. Window frame – Summary
      2. Window frame – Exercise 2
      3. Window frame – Exercise 3
      4. Window frame – Exercise 4
   6. Analytic functions
      1. Analytic functions – Summary
      2. Analytic functions – Exercise 2
      3. Analytic functions – Exercise 3
      4. Analytic functions – Exercise 4
   7. ORDER BY PARTITION BY
      1. PARTITION BY ORDER BY – Summary
      2. PARTITION BY ORDER BY – Exercise 1
      3. PARTITION BY ORDER BY – Exercise 2
   8. Order of evaluation
      1. Ranking of platforms
      2. Grouping by ntiles
   9. Summary
      1. Congratulations

10. 10. Final Quiz

    0/15

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

    List of exercises Buy course
    1. Introduction
       1. Welcome to the Final Quiz
       2. Table doctor
       3. Table procedure
    2. Final quiz
       1. Question 1
       2. Question 2
       3. Question 3
       4. Question 4
       5. Question 5
       6. Question 6
       7. Question 7
       8. Question 8
       9. Question 9
       10. Question 10
       11. Question 11
    3. Summary
       1. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more