Window Functions in MySQL 8

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. The dog table
      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. The department table
      3. The purchase table
   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. The route table
      3. The journey table
      4. The ticket table
   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. The purchase table
   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. The application table
      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. The stock_change table
      3. The single_order table
      4. The order_position table
   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. The revenue table
      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. The website table
      3. The statistics table
   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. The advertisement table
      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. The store table
      3. The sales table
   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. The repairs table
      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 MySQL query? Let's find out.

   List of exercises Buy course
   1. When window functions are evaluated
      1. Introduction
      2. The auction table
      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. The book table
      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. The customer table
      3. The movie table
      4. The review table
      5. The single_rental table
      6. The subscription table
      7. The giftcard table
   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. PARTITION BY ORDER 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. The doctor table
       3. The medical_procedure table
    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