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.

Course

Advanced

Window Functions in MS SQL Server

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

Table of contents

21 free interactive coding challenges

Lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

limited to this course only

$39

Buy now

Want a better deal?

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

Get unlimited lifetime access to all 65 present and future courses

Save $450

Unlimited lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

to all 65 present and future courses

$149

$599

75% OFF

Buy now
Details
214

Interactive exercises

20 h

Estimated time

8597

Users enrolled

☆☆☆☆☆
★★★★★

537 ratings

Description Table of contents Reviews Start learning

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.

Cover image for the course 'Window Functions in MS SQL Server'

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

Free

Paid content

0%

Course progress

0/214

Exercises completed

1.

Introduction

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

0/8
Start now

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
Start now

Get to know the data

Introduction
The Department table
The Purchase table

Get to know the OVER() clause

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

Computations with OVER()

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

Aggregate functions with OVER()

OVER() and COUNT()
Practice 1
Practice 2

Using OVER() with WHERE

How OVER() works
Using OVER() with WHERE

Summary

Summary
Exercise 1
Exercise 2
Congratulations

3.

OVER(PARTITION BY)

Discover how to define a function window with PARTITION BY

0/16

Introduction

Introduction
The Route table
The Journey table
The Ticket table

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

Summary

Summary
The Employee table
Exercise 1
Exercise 2
Congratulations

4.

Ranking Functions

Learn how you can rank rows with window functions

0/26

Introduction

Introduction
The Purchase table

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

Selecting n-th row

Explanation
Create a ranking
The full query
Practice 1
Practice 2

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

Introduction

Introduction
The StockChange table
The SingleOrder table
The OrderPosition table

ROWS

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

RANGE

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

Default window frame

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

Summary

Summary
The Revenue table
Exercise 1
Exercise 2
Exercise 3
Congratulations

6.

Analytic functions

Learn the most essential analytic functions

0/28

Introduction

Introduction
The Website table
The Statistics table

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

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

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

Introduction

Introduction
The Store Table
The Sales Table

Quick Refresher

Introduction
PARTITION BY – Refresher 1
PARTITION BY – Refresher 2

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!

PARTITION BY ORDER BY with Window Frames

Explanation
Practice Time!
More Practice Time!

Using PARTITION BY ORDER BY with Analytical Functions

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

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

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

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

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

Introduction

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

Simple OVER()

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

PARTITION BY

PARTITION BY – Review and Exercise 1
PARTITION BY – Exercise 2
PARTITION BY – Exercise 3
PARTITION BY – Exercise 4

Ranking functions

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

Window Frames

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

Analytic Functions

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

PARTITION BY ORDER BY

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

Order of Evaluation

Ranking of Platforms
Grouping by NTILE

Finished!

Congratulations

10.

Final quiz

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

0/15

Introduction

Welcome to the Final Quiz
The Doctor Table
The Treatment Table

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

You’ve Completed the Windows Function Course!

Congratulations!

Reviews (13)

Average rating

4.81/5

☆☆☆☆☆
★★★★★

537 ratings

Details

5 stars

85%

4 stars

14%

3 stars

1%

2 stars

0%

1 stars

0%