- NaN hours only!

Advanced Course

Window Functions

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

4.90 ★★★★★ ★★★★★ (2,819 ratings)

54,016 learners enrolled

Lifetime access

limited to this course only

$39

Want a better deal?

Get unlimited lifetime access to all 73 present and future courses

Save $450

Unlimited lifetime access

to all 73 present and future courses

$149 $599

29h : 28m : 37s 75% OFF

Free trial Yes

Certificate of completion Yes

Time to complete 20 h

Coding challenges 218

Skills you will gain

  • Learn the syntax of the OVER() clause
  • Learn how to combine OVER() and PARTITION BY
  • Discover how to combine OVER() and ORDER BY
  • Understand how to rank rows using RANK, DENSE_RANK, and ROW_NUMBER
  • Learn how to create sophisticated window frames using ROWS and RANGE.
  • Discover the syntax of the analytic functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTILE.
  • Understand how to combine window functions and GROUP BY
  • Learn when to use window functions and when to use GROUP BY
  • Learn how window functions can be used to build rankings
  • Compute running totals and running averages
  • Find the best and worst performers
  • Learn how to Investigate trends across time
  • Calculate contributions to the whole, such as commission percentages
  • Gain a deeper understanding of SQL aggregate functions.

Last reviews

slide 2 of 3

Description

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

Course progress 0%

Exercises completed 0/218

  1. Introduction

    0/8

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

  2. OVER()

    0/17

    Your first encounter with window functions.

  3. OVER(PARTITION BY)

    0/16

    Discover how you can define the function window with PARTITION BY

  4. Ranking Functions

    0/26

    Learn how you can rank rows with window functions.

  5. Window Frame

    0/26

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

  6. Analytics functions

    0/30

    Learn the most essential analytic functions.

  7. PARTITION BY ORDER BY

    0/25

    Create advanced statistics computed independently for various groups of rows.

  8. Window functions - evaluation order

    0/23

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

  9. Practice field

    0/32

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

  10. Final Quiz

    0/15

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

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Reviews (16)

Average rating

4.90/5

★★★★★ ★★★★★ (2819 ratings)

5 stars

92%

4 stars

8%

3 stars

0%

2 stars

0%

1 stars

0%

★★★★★
★★★★★
01/02/2025

Excellent course, with a great flow of exercise, it leaves no stone unturned for getting you master writing window function queries.

Mohammad Shariq

★★★★★
★★★★★
12/31/2024

Very complete and easy to follow. Some exercises are tricky, but in the real life challenges can even be more difficult, so I have no complains!

José Francisco Prieto Montes

★★★★★
★★★★★
09/19/2023

I faced considerable difficulty in grasping the concept of window functions initially. However, the course developed by the LearnSQL team proved to be an invaluable resource. The course was meticulously structured, featuring an array of practice questions that played a pivotal role in deepening my comprehension of these complex concepts. I wholeheartedly recommend embarking on this course, as it has been instrumental in enhancing my understanding of SQL window functions. Kudos to the LearnSQL team for their outstanding work! :)

N Vinay

★★★★★
★★★★★
06/29/2023

PRETTY GOOD, RECOMMENDED

REINIS RUDZITIS

★★★★★
★★★★★
06/24/2023

Damn good one

Prachi Harley

★★★★★
★★★★★
02/28/2023

Absolutely amazing, concise, and effective course. Cannot be happier. I came in with Windows knowledge and this course took me to the next level and has made my comfortability skyrocket.

jonathanfig4@gmail.com jonathanfig4@gmail.com

★★★★★
★★★★★
12/31/2022

Excellent! Helps to write complex queries concisely.

Isis Santos Costa

★★★★★
★★★★★
12/30/2022

Good course. Some exercises should be explained in more detail though

Miguel Rooney

★★★★★
★★★★★
10/05/2022

challenging enough, not too repetitive

Ling Chin

★★★★★
★★★★★
09/29/2022

Excellent Course for understanding Window functions in SQL

Andre Vergeer