Window Functions in MS SQL Server

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.

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

Free

Paid content

Course progress

0/214

Exercises completed

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

0/8

Exercises completed

Start now

Introduction

Welcome!

What will I learn?

The Dog table

Quiz – Question 1

Quiz – Question 2

Quiz – Question 3

Quiz – Question 4

Summary

Your first encounter with window functions

0/17

Exercises completed

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

Exercise 1

Exercise 2

Congratulations

Discover how to define a function window with PARTITION BY

0/16

Exercises completed

Upgrade

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

The Employee table

Exercise 1

Exercise 2

Congratulations

Learn how you can rank rows with window functions

0/26

Exercises completed

Upgrade

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

The Application table

Exercise 1

Exercise 2

Congratulations

Learn how to create sophisticated window frames for your window functions

0/26

Exercises completed

Upgrade

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

The Revenue table

Exercise 1

Exercise 2

Exercise 3

Congratulations

Learn the most essential analytic functions

0/28

Exercises completed

Upgrade

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

The Advertisement table

Exercise 1

Exercise 2

Exercise 3

Congratulations

Create advanced statistics computed independently for various groups of rows

0/24

Exercises completed

Upgrade

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!

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

0/23

Exercises completed

Upgrade

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!

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

0/31

Exercises completed

Upgrade

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 – Revew 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

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

0/15

Exercises completed

Upgrade

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!

Average rating

4.86 / 5

☆☆☆☆☆

★★★★★

15 ratings

Details

5 stars

87%

4 stars

13%

3 stars

2 stars

1 stars

Description

What are the requirements?

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

Who Should Take This Course?

Table of contents

1. Introduction

2. The OVER() clause

3. OVER(PARTITION BY)

4. Ranking Functions

5. Window Frame

6. Analytic functions

7. PARTITION BY ORDER BY

8. All You Need to Know About Using Window Functions

9. Window Functions: The Practice Field

10. Final quiz

Reviews (12)