Window Functions in MySQL 8

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