Window Functions in MS SQL Server

Table of contents

1. 1. Introduction

   0/8

   Window functions? We'll explain what they're all about

   List of exercises Start now
   1. Introduction
      1. Welcome!
      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. The OVER() clause

   0/17

   Your first encounter with window functions

   List of exercises Start now
   1. Get to know the data
      1. Introduction
      2. The Department table
      3. The Purchase table
   2. Get to know the OVER() clause
      1. Introducing Window Functions
      2. What is OVER()?
      3. Practice time!
   3. Computations with OVER()
      1. Computations with OVER() – part 1
      2. Computations with OVER() – part 2
   4. Aggregate functions with OVER()
      1. OVER() and COUNT()
      2. Practice 1
      3. Practice 2
   5. Using OVER() with WHERE
      1. How OVER() works
      2. Using OVER() with WHERE
   6. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Congratulations

3. 3. OVER(PARTITION BY)

   0/16

   Discover how to define a 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. Introducing PARTITION BY
      2. Using PARTITION BY
      3. The range of OVER(PARTITION BY)
      4. Using PARTITION BY with 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. The Employee 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. 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 and multiple 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 a 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

   Learn how to create sophisticated window frames for your window functions

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. The StockChange table
      3. The SingleOrder table
      4. The OrderPosition table
   2. ROWS
      1. General syntax
      2. Defining the window frame
      3. First exercise
      4. ROWS – practice 1
      5. ROWS – practice 2
      6. ROWS – practice 3
      7. Abbreviations
      8. Abbreviations – exercise
   3. RANGE
      1. RANGE explained
      2. ROWS and RANGE – what's the difference?
      3. Setting RANGE boundaries
      4. Range – practice 1
      5. Range – practice 2
      6. Range – practice 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. Analytic functions

   0/28

   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. The LEAD(x) function
      3. LEAD(x) – practice
      4. Deltas
      5. The LEAD(x, y) function
      6. The LEAD(x, y, z) function
      7. The LAG(x) function
      8. The LAG(x, y) function
      9. The LAG(x, y, z) function
      10. Practice 1
      11. Practice 2
   3. FIRST_VALUE(), LAST_VALUE()
      1. The FIRST_VALUE(x) function
      2. FIRST_VALUE(x) – exercise
      3. The LAST_VALUE(x) function
      4. LAST_VALUE(x) with window frame
      5. LAST_VALUE(x) – practice
      6. LAST_VALUE(x) with different sorting
      7. Practice 1
      8. Practice 2
   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/24

   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. Introduction
      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. Using PARTITION BY ORDER BY in a CTE
      4. Practice Time!
      5. More Practice Time!
      6. Even More Practice!
   4. PARTITION BY ORDER BY with Window Frames
      1. Explanation
      2. Practice Time!
      3. More Practice Time!
   5. Using PARTITION BY ORDER BY with Analytical Functions
      1. LEAD() with PARTITION BY ORDER BY
      2. FIRST_VALUE() with PARTITION BY ORDER BY
      3. Practice Time!
   6. Summary and Review
      1. Summary
      2. The Repairs Table
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Congratulations!

8. 8. All You Need to Know About Using Window Functions

   0/23

   Do you know when window functions are evaluated in a T-SQL query? Let's find out!

   List of exercises Buy course
   1. Evaluation Order and Common Problems
      1. Introduction
      2. Get to Know the Auction Table
      3. Query Evaluation Order: Problems with WHERE
      4. Solving WHERE Problems with Subqueries
      5. Query Evaluation Order – Problems with HAVING
      6. Solving HAVING Problems with Subqueries
      7. Query Evaluation Order: Problems with GROUP BY
      8. Solving GROUP BY Problems with Subqueries
      9. Window Functions in ORDER BY
   2. Window functions and GROUP BY
      1. What Window Functions See - Part 1
      2. What Window Functions See - Part 2
      3. Practice Time!
      4. More Practice Time!
      5. Ranking with Aggregate Functions
      6. Practice Time!
      7. Day-to-Day Deltas with GROUP BY
      8. Grouped Rows, Window Functions, and PARTITION BY
   3. Summary and Review
      1. Summary
      2. The Book Table
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Congratulations!

9. 9. Window Functions: The Practice Field

   0/31

   Master all the skills you've acquired so far with our practice set

   List of exercises Buy course
   1. Introduction
      1. Welcome to the Practice Field
      2. The Customer Table
      3. The Movie Table
      4. The Review Table
      5. The SingleRental Table
      6. The Subscription Table
      7. The GiftCard Table
   2. Simple OVER()
      1. OVER() – Review and Exercise 1
      2. OVER() – Exercise 2
      3. OVER() – Exercise 3
   3. PARTITION BY
      1. PARTITION BY – Review and Exercise 1
      2. PARTITION BY – Exercise 2
      3. PARTITION BY – Exercise 3
      4. PARTITION BY – Exercise 4
   4. Ranking functions
      1. Ranking Functions – Review and Exercise 1
      2. Ranking Functions – Exercise 2
      3. Ranking Functions – Exercise 3
      4. Ranking Functions – Exercise 4
   5. Window Frames
      1. Window Frames – Review and Exercise 1
      2. Window Frames – Exercise 2
      3. Window Frames – Exercise 3
      4. Window Frames – Exercise 4
   6. Analytic Functions
      1. Analytic Functions – Review and Exercise 1
      2. Analytic Functions – Exercise 2
      3. Analytic Functions – Exercise 3
   7. PARTITION BY ORDER BY
      1. PARTITION BY ORDER BY – Review and Exercise 1
      2. PARTITION BY ORDER BY – Exercise 2
      3. PARTITION BY ORDER BY – Exercise 3
   8. Order of Evaluation
      1. Ranking of Platforms
      2. Grouping by NTILE
   9. Finished!
      1. Congratulations

10. 10. Final quiz

    0/15

    Test all the skills you've acquired so far with this comprehensive final quiz

    List of exercises Buy course
    1. Introduction
       1. Welcome to the Final Quiz
       2. The Doctor Table
       3. The Treatment Table
    2. The 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. You’ve Completed the Windows Function Course!
       1. Congratulations!

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more