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.

Advanced

Interactive Course

Window Functions in MS SQL Server

Learn how to use analytic T-SQL functions to deeply understand data.

Table of contents
214

Interactive courses

20 h

Estimated time

37

Users enrolled

☆☆☆☆☆
★★★★★

3 ratings

Description Table of contents Reviews Start for free

Description

T-SQL window functions – also known as windowing functions, OVER functions, or analytic functions – are tremendously useful. These functions make building complex aggregations much simpler. In this step-by-step tutorial, we will lead you through T-SQL window functions. At its end, you'll embrace this topic with ease and feel comfortable using window functions in SQL Server databases.

Window functions are so powerful that they serve as a dividing point in time: people talk about SQL Server before window functions and SQL Server after window functions. Because they were introduced into the standard relatively recently, these functions aren't covered in many T-SQL courses.

About the Window Functions in SQL Server Course
This course covers the syntax and semantics of T-SQL window functions. It shows how powerful they are, what the typical use cases are, and how to use ORDER BY and PARTITION BY to set up a frame for window functions. You'll also learn the difference between ROWS and RANGE clauses

This course is intended for intermediate users. We assume the user knows the basics of T-SQL, including:

  • How to select from a single table, including writing complex WHERE conditions
  • How to JOIN tables
  • How GROUP BY and HAVING work

What are the requirements?

  • A web browser
  • Knowledge of basic T-SQL, including JOINs and GROUP BY clauses

What Am I Going To Get from This SQL Server Course?

You'll discover how window functions can be used to:
  • Compute running totals and running averages
  • Build rankings
  • Find best and worst performers
  • Investigate trends across time
  • Calculate contributions to the whole, such as commission percentages

You'll also get a deeper understanding of T-SQL aggregate functions.

Who Should Take This Course?

  • Beginning database analysts
  • Developers who want to keep their knowledge of SQL Server current
  • Students taking classes in relational databases
  • Anyone who wants to learn SQL window functions

Table of contents

Trial

0%

Completed

0/214

Exercises done

1. Introduction

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

0/8

Exercises done

Start now
arrow

Introduction

Welcome!
What will I learn?
The Dog table
Quiz – Question 1
Quiz – Question 2
Quiz – Question 3
Quiz – Question 4
Summary

2. The OVER() clause

Your first encounter with window functions

0/17

Exercises done

Start now
arrow

Get to know the data

Introduction
The Department table
The Purchase table
arrow

Get to know the OVER() clause

Introducing Window Functions
What is OVER()?
Practice time!
arrow

Computations with OVER()

Computations with OVER() – part 1
Computations with OVER() – part 2
arrow

Aggregate functions with OVER()

OVER() and COUNT()
Practice 1
Practice 2
arrow

Using OVER() with WHERE

How OVER() works
Using OVER() with WHERE
arrow

Summary

Summary
Exercise 1
Exercise 2
Congratulations

3. OVER(PARTITION BY)

Discover how to define a function window with PARTITION BY

0/16

Exercises done

Start now
arrow

Introduction

Introduction
The Route table
The Journey table
The Ticket table
arrow

PARTITION BY

Introducing PARTITION BY
Using PARTITION BY
The range of OVER(PARTITION BY)
Using PARTITION BY with multiple columns
OVER(PARTITION BY) – practice 1
OVER(PARTITION BY) – practice 2
OVER(PARTITION BY) – practice 3
arrow

Summary

Summary
The Employee table
Exercise 1
Exercise 2
Congratulations

4. Ranking Functions

Learn how you can rank rows with window functions

0/26

Exercises done

Start now
arrow

Introduction

Introduction
The Purchase table
arrow

Ranking functions

Ranking functions – introduction
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 and multiple columns
Ranking and ORDER BY
ROW NUMBER with ORDER BY
NTILE(X)
NTILE(X) – practice
arrow

Selecting n-th row

Explanation
Create a ranking
The full query
Practice 1
Practice 2
arrow

Summary

Summary
The Application table
Exercise 1
Exercise 2
Congratulations

5. Window Frame

Learn how to create sophisticated window frames for your window functions

0/26

Exercises done

Start now
arrow

Introduction

Introduction
The StockChange table
The SingleOrder table
The OrderPosition table
arrow

ROWS

General syntax
Defining the window frame
First exercise
ROWS – practice 1
ROWS – practice 2
ROWS – practice 3
Abbreviations
Abbreviations – exercise
arrow

RANGE

RANGE explained
ROWS and RANGE – what's the difference?
Setting RANGE boundaries
Range – practice 1
Range – practice 2
Range – practice 3
arrow

Default window frame

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

Summary

Summary
The Revenue table
Exercise 1
Exercise 2
Exercise 3
Congratulations

6. Analytic functions

Learn the most essential analytic functions

0/28

Exercises done

Start now
arrow

Introduction

Introduction
The Website table
The Statistics table
arrow

LEAD() and LAG()

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

FIRST_VALUE(), LAST_VALUE()

The FIRST_VALUE(x) function
FIRST_VALUE(x) – exercise
The LAST_VALUE(x) function
LAST_VALUE(x) with window frame
LAST_VALUE(x) – practice
LAST_VALUE(x) with different sorting
Practice 1
Practice 2
arrow

Summary

Summary
The Advertisement table
Exercise 1
Exercise 2
Exercise 3
Congratulations

7. PARTITION BY ORDER BY

Create advanced statistics computed independently for various groups of rows

0/24

Exercises done

Start now
arrow

Introduction

Introduction
The Store Table
The Sales Table
arrow

Quick Refresher

Introduction
PARTITION BY – Refresher 1
PARTITION BY – Refresher 2
arrow

PARTITION BY ORDER BY with Ranking

RANK() with PARTITION BY ORDER BY
NTILE(x) with PARTITION BY ORDER BY
Using PARTITION BY ORDER BY in a CTE
Practice Time!
More Practice Time!
Even More Practice!
arrow

PARTITION BY ORDER BY with Window Frames

Explanation
Practice Time!
More Practice Time!
arrow

Using PARTITION BY ORDER BY with Analytical Functions

LEAD() with PARTITION BY ORDER BY
FIRST_VALUE() with PARTITION BY ORDER BY
Practice Time!
arrow

Summary and Review

Summary
The Repairs Table
Exercise 1
Exercise 2
Exercise 3
Congratulations!

8. All You Need to Know About Using Window Functions

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

0/23

Exercises done

Start now
arrow

Evaluation Order and Common Problems

Introduction
Get to Know the Auction Table
Query Evaluation Order: Problems with WHERE
Solving WHERE Problems with Subqueries
Query Evaluation Order – Problems with HAVING
Solving HAVING Problems with Subqueries
Query Evaluation Order: Problems with GROUP BY
Solving GROUP BY Problems with Subqueries
Window Functions in ORDER BY
arrow

Window functions and GROUP BY

What Window Functions See - Part 1
What Window Functions See - Part 2
Practice Time!
More Practice Time!
Ranking with Aggregate Functions
Practice Time!
Day-to-Day Deltas with GROUP BY
Grouped Rows, Window Functions, and PARTITION BY
arrow

Summary and Review

Summary
The Book Table
Exercise 1
Exercise 2
Exercise 3
Congratulations!

9. Window Functions: The Practice Field

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

0/31

Exercises done

Start now
arrow

Introduction

Welcome to the Practice Field
The Customer Table
The Movie Table
The Review Table
The SingleRental Table
The Subscription Table
The GiftCard Table
arrow

Simple OVER()

OVER() – Review and Exercise 1
OVER() – Exercise 2
OVER() – Exercise 3
arrow

PARTITION BY

PARTITION BY – Revew and Exercise 1
PARTITION BY – Exercise 2
PARTITION BY – Exercise 3
PARTITION BY – Exercise 4
arrow

Ranking functions

Ranking Functions – Review and Exercise 1
Ranking Functions – Exercise 2
Ranking Functions – Exercise 3
Ranking Functions – Exercise 4
arrow

Window Frames

Window Frames – Review and Exercise 1
Window Frames – Exercise 2
Window Frames – Exercise 3
Window Frames – Exercise 4
arrow

Analytic Functions

Analytic Functions – Review and Exercise 1
Analytic Functions – Exercise 2
Analytic Functions – Exercise 3
arrow

PARTITION BY ORDER BY

PARTITION BY ORDER BY – Review and Exercise 1
PARTITION BY ORDER BY – Exercise 2
PARTITION BY ORDER BY – Exercise 3
arrow

Order of Evaluation

Ranking of Platforms
Grouping by NTILE
arrow

Finished!

Congratulations

10. Final quiz

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

0/15

Exercises done

Start now
arrow

Introduction

Welcome to the Final Quiz
The Doctor Table
The Treatment Table
arrow

The Final Quiz

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

You’ve Completed the Windows Function Course!

Congratulations!

Reviews (0)

Average rating

5.00 / 5

☆☆☆☆☆
★★★★★

3 ratings

Details

5 stars

100%

4 stars

0%

3 stars

0%

2 stars

0%

1 stars

0%