Course

Advanced

Window Functions

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

Table of contents

10 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 68 present and future courses

Save $450

Unlimited lifetime access

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

to all 68 present and future courses

$149

$599

75% OFF

Buy now
Details
218

Interactive exercises

20 h

Estimated time

51776

Users enrolled

☆☆☆☆☆
★★★★★

2814 ratings

Description

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 SQL window functions. At its end, you'll embrace this topic with ease and feel comfortable using window functions in SQL databases.

Window functions SQL clauses floating on the screen

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

Note: Most popular database engines support window functions.

About the Window Functions in SQL Course

This course covers the syntax and semantics of 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 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 SQL, including JOINs and GROUP BY clauses

What Am I Going to Get from This SQL Course?

In this course you will learn:

  • The syntax of the OVER() clause
  • How to combine OVER() and PARTITION BY
  • How to combine OVER() and ORDER BY
  • How to rank rows using RANK, DENSE_RANK, and ROW_NUMBER
  • How to create sophisticated window frames using ROWS and RANGE.
  • The syntax of the analytic functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTILE.
  • How to combine window functions and GROUP BY
  • When to use window functions and when to use GROUP BY

You'll discover how window functions can be used to:

  • Build rankings
  • Compute running totals and running averages
  • Find the 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 SQL aggregate functions.

Who Should Take This Course?

  • Beginning database analysts
  • Developers who want to keep their knowledge of SQL 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/218

Exercises completed

1.

Introduction

Window functions? We'll explain what it's all about.

0/8

Introduction

2.

OVER()

Your first encounter with window functions.

0/17

Get to know tables

OVER()

Computations with OVER()

Aggregate functions with OVER()

OVER() and WHERE

Summary

3.

OVER(PARTITION BY)

Discover how you can define the function window with PARTITION BY

0/16

Introduction

PARTITION BY

Summary

4.

Ranking Functions

Learn how you can rank rows with window functions.

0/26

Introduction

Ranking functions

Selecting n-th row

Summary

5.

Window Frame

Get to know how to create sophisticated window frames for your window functions.

0/26

Introduction

ROWS

RANGE

Default window frame

Summary

6.

Analytics functions

Learn the most essential analytic functions.

0/30

Introduction

LEAD and LAG

FIRST_VALUE, LAST_VALUE, NTH_VALUE

Summary

7.

PARTITION BY ORDER BY

Create advanced statistics computed independently for various groups of rows.

0/25

Introduction

Quick refresher

PARTITION BY ORDER BY with ranking

PARTITION BY ORDER BY with window frames

PARTITION BY ORDER BY with analytical functions

Summary

8.

Window functions - evaluation order

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

0/23

When window functions are evaluated

Window functions and GROUP BY

Summary

9.

Practice field

Master all the skills you acquired so far in our big practice part.

0/32

Introduction

Simple OVER()

PARTITION BY

Ranking functions

Window frame

Analytic functions

ORDER BY PARTITION BY

Order of evaluation

Summary

10.

Final Quiz

Test the skills you acquired in the whole course with this final quiz.

0/15

Introduction

Final quiz

Summary

Reviews (14)

Average rating

4.90/5

☆☆☆☆☆
★★★★★

2814 ratings

Details

5 stars

92%

4 stars

8%

3 stars

0%

2 stars

0%

1 stars

0%