Ad-blocking extension has been detected. Please disable it. It may cause problems.

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

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

214

Interactive exercises

20 h

Estimated time

8362

Users enrolled

☆☆☆☆☆

★★★★★

537 ratings

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.

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

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

- 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.

- 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

0%

Course progress

0/214

Exercises completed

1.

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

2.

Your first encounter with window functions

Get to know the data

Get to know the OVER() clause

Computations with OVER()

Aggregate functions with OVER()

Using OVER() with WHERE

Summary

3.

Discover how to define a function window with PARTITION BY

Introduction

PARTITION BY

4.

Learn how you can rank rows with window functions

Introduction

Ranking functions

Selecting n-th row

5.

Learn how to create sophisticated window frames for your window functions

ROWS

RANGE

Default window frame

6.

Learn the most essential analytic functions

Introduction

LEAD() and LAG()

FIRST_VALUE(), LAST_VALUE()

7.

Create advanced statistics computed independently for various groups of rows

Introduction

Quick Refresher

PARTITION BY ORDER BY with Ranking

PARTITION BY ORDER BY with Window Frames

Using PARTITION BY ORDER BY with Analytical Functions

Summary and Review

8.

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

Evaluation Order and Common Problems

Window functions and GROUP BY

Summary and Review

9.

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

Introduction

PARTITION BY

Ranking functions

Window Frames

Analytic Functions

PARTITION BY ORDER BY

Order of Evaluation

Finished!

10.

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

Introduction

The Final Quiz

You’ve Completed the Windows Function Course!

Average rating

4.81/5

☆☆☆☆☆

★★★★★

537 ratings

Details

5 stars

85%

4 stars

14%

3 stars

1%

2 stars

0%

1 stars

0%