Course

AdvancedWant to stay up to date with the latest MySQL features? MySQL window functions are essential for doing modern data analysis. Learn how to use them to take your analysis skills to the next level!

Want to master **MySQL window functions** and significantly increase the expressive power of your SQL queries? This course, geared for intermediate MySQL 8 users, will show you how to perform complex computations in one simple query.

Window functions were introduced to MySQL 8.0 in 2018. They immediately streamlined and facilitated the daily work of analysts, data scientists, and data engineers. They are also an essential feature for anyone doing business analysis in MySQL 8 or above.

Window functions are similar to aggregate functions: they compute a value for a group of rows. In window functions, this group is called a window frame. Unlike aggregate functions and GROUP BY, window functions do not collapse rows and return one value for the entire group; they keep details of each individual row and show the group value.

In SQL, window functions let you easily process time series data (e.g. monthly financial results, daily website visits, quarterly sales totals, etc.). Use them to build SQL reports that:

- Create
**rankings**: who the best salesperson is, what is the best-selling product, which product categories sell the least, etc. - Compute
**running totals**: accumulated sales for each day of the month, how many users registered up to a certain day, etc. - Calculate
**moving averages**: the weekly average sale, monthly average expenses, etc. - Find day-to-day, month-to-month, or year-to-year differences (
**deltas**), i.e. the increase/decrease between different time periods.

*Window Functions in MySQL 8* is split into multiple parts. Each part covers one aspect of window functions: the OVER() clause, PARTITION BY, ORDER BY, defining window frames, etc. You will also learn different types of window functions, including:

- Aggregate functions
- Ranking functions (RANK(), DENSE_RANK(), and ROW_NUMBER())
- Positional functions (LEAD() and LAG())

You’ll also practice using window functions with GROUP BY. Finally, you’ll learn when to use window functions and when to use GROUP BY.

The course is **interactive**. You’ll learn by writing real SQL queries in our online console and immediately seeing the results. Our platform runs your code and verifies your solution. Each exercise includes an instruction and a task that you complete. This way, you can put your new knowledge into practice right away.

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

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

In this MySQL 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 in MySQL 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

- Beginning database analysts working with MySQL 8
- Developers who want to keep their knowledge of MySQL up to date
- Students taking classes in relational databases using MySQL 8
- Anyone who wants to learn window functions in MySQL

**218 interactive exercises**. Learn at your own pace, from anywhere and at any time. Interactive, hands-on learning improves retention.**Lifetime access to the course**. When you purchase the course, you’ll get instant personal access to all of its content.**Certificate of completion**. After you successfully finish all of the exercises, you’ll get a downloadable PDF certificate to showcase your accomplishment.**30-day money-back guarantee**. If you’re not satisfied with the quality of the course, you can get a refund within 30 days of your purchase.**Hints and help**. There are hints available in the exercises to help you if you get stuck. You can also ask our learning community for help through the Disqus tab. Or drop the course creators a line – we’ll be more than happy to answer! 😉

0%

Course progress

0/218

Exercises completed

1.

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

2.

Your first encounter with window functions.

3.

Discover how you can define the function window with PARTITION BY

4.

Learn how you can rank rows with window functions.

5.

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

6.

Learn the most essential analytic functions.

7.

Create advanced statistics computed independently for various groups of rows.

8.

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

9.

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

10.

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