Advanced Course
Window Functions Practice Set
Do you want to practice SQL window functions? You've come to the right place! Here are 100 interactive online exercises on window functions on three different real-life databases.
4.90
9,764 learners enrolled
Free trial Yes
Certificate of completion Yes
Time to complete 20 h
Coding challenges 100
Skills you will gain
- Practice the use of the OVER() clause
- Rediscover how to combine OVER() and PARTITION BY
- Review how to combine OVER() and ORDER BY
- Review h to rank rows using RANK, DENSE_RANK, and ROW_NUMBER
- Rediscover how to create sophisticated window frames using ROWS and RANGE.
- Reinforce your knowledge of the analytic functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTILE.
- Rank objects with window functions RANK, DENSE_RANK, and ROW_NUMBER.
- Find top elements using window functions.
- Compute the difference (delta) between two rows.
- Calculate running totals in SQL.
- Calculate moving averages in SQL.
- Analyze a time series in SQL.
- Combine window functions and GROUP BY
- Review when to use window functions and when to use GROUP BY
Last reviews
Description
Window functions are a powerful modern addition to SQL. They allow you to prepare complex SQL reports: rankings, differences between two rows, running totals, moving averages, time series analysis, and more.
Are you dreaming of a career in data analysis? You need to know SQL window functions if you want to move freely in the modern SQL environment. This course offers a unique opportunity to practice SQL window functions interactively.
Window functions are rarely covered in a typical SQL course curriculum, and exercise sets for window functions are even more scarce. This course is designed to fill this gap in the market. To take full advantage of this course, you should know at least the basics of window functions. You don't know anything about them? Take the Window Functions course first and come back for more.
This course offers multiple exercises designed to make you practice writing realistic reports using SQL window functions. Their syntax is complex and difficult to remember. You need practice to grasp the syntax and be comfortable with creating complex reports with window functions.
The course uses three different datasets. In the first part of the course, you will work with data from an online store. You will rank products, compute statistics for customers and their typical order size, divide customers into categories, etc. In the second part, you will deal with data from track running competitions. You can find out the fastest 100m runner, the average pace for the 10000m, the country with the most medals in the women’s marathon, etc. In the third part, you will train with traffic data from a blog of an online pet product store. You will find out which articles and article categories lead to sales the most, which author generates the biggest traffic, which articles are the most popular, when they reach the height of their popularity, etc.
We created our exercises so that you would not only practice window functions but also use the SQL queries you would actually write in your projects. The datasets and the reports are diverse and useful. It’s very likely that you will work with similar data in real life: transaction data similar to that of a store, marketing data similar to that of a blog, or achievement data in different categories similar to that of a sports competition.
The course uses the standard SQL syntax that can be used in all databases that support window functions.
This SQL course is interactive. You'll practice writing actual SQL queries to solve exercises in your web browser. Our web-based platform will run your command and verify your solution. You don't need to install anything on your computer, so you can start practicing right away.
What Do You Need to Take This Course?
- An Internet connection and a web browser.
- Knowledge of SQL window functions.
This Course Will Make You:
- Review the syntax of SQL window functions:
- 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 analytic functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTILE.
- Practice common usages of SQL window functions:
- Ranking objects with window functions RANK, DENSE_RANK, and ROW_NUMBER.
- Finding top elements using window functions.
- Computing the difference (delta) between two rows.
- Calculating running totals in SQL.
- Calculating moving averages in SQL.
- Analyzing a time series in SQL.
- Combining window functions and GROUP BY
- When to use window functions and when to use GROUP BY
- And more.
- Work with three different realistic data sets.
Who Should Take This Course?
- Data analysts working with SQL databases.
- Programmers wishing to keep their SQL knowledge current.
- Computer science students interested in modern SQL.
- Anyone who wants to practice SQL window functions.
What's in it for me?
- 100 interactive exercises. Learn at your own pace, from anywhere and anytime. Interact with hands-on exercises for improved retention.
- Lifetime access to the course. When you purchase the course, you'll get instant personal access to all of its content.
- Certification. Complete all of the exercises successfully to get a certificate. You can publish the certificate on your LinkedIn profile (here’s how).
- 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 for the exercises. You can make use of ready-made hints for exercises, or ask questions and share insights with other members of our community through the Discuss tab. You can also drop us a line at contact@learnsql.com, and we'll be more than happy to answer! 😉
Table of contents
Course progress 0%
Exercises completed 0/100
-
Window Functions Practice – Northwind Database
0/34Practice using the window functions when writing the SQL queries.
-
Window Functions Practice – Runs
0/31Practice using the window functions when writing the SQL queries.
-
Window Functions Practice – Blog
0/35Practice using the window functions when writing SQL queries.
Get the Certificate of Competency in SQL
This certificate will confirm your practical SQL problem-solving skills
