Back to articles list Articles Cookbook
20 minutes read

SQL Practice: 11 SQL Window Functions Practice Exercises with Detailed Solutions

In this article, we present 11 practice exercises involving SQL window functions, along with solutions and detailed explanations.

SQL window functions are a powerful feature that lets us extract meaningful insights from our data easily, yet few SQL courses offer SQL window functions exercises. This makes practicing window functions quite difficult. In this article, we’ll give you 11 practice exercises that feature window functions.

All exercises shown in this article come from our interactive courses Window Functions and Window Functions Practice Set. The Window Functions course is an in-depth tutorial with over 200 exercises. We recommend this course to learn or review SQL window functions knowledge. Window Functions Practice Set is a practice course for those who know SQL window functions and are looking for more practice.

Window Functions in a Nutshell

SQL window functions are tools that help analyze data in different ways. From computing running totals and moving averages, comparing data within specific subsets, and identifying top performers, to achieving intricate ranking and partitioning, these functions empower us to extract deeper insights from our data – and accomplish complex tasks easily.

SQL window functions offer a versatile toolkit for enhanced data analysis. This toolkit includes:

Furthermore, the OVER() clause enables precise data partitioning and ordering within these functions, enabling users to perform complex calculations on defined subsets of data.

Mastering SQL window functions is becoming increasingly crucial for data professionals, analysts, and engineers. It not only empowers them to efficiently solve complex analytical challenges, it also provides a deeper understanding of data. Practicing SQL window functions will improve your ability to create advanced queries and help you discover new insights from data. This is a vital skill in today's data-focused world.

Before tackling the exercises, you may want to take a look at our window functions cheat sheet, which will remind you of the list of window functions and their syntax.

SQL Window Functions Practice Exercises: Online Movie Store

Before we start the exercises, let’s check out the dataset we’ll be using.

Dataset

The following exercises use the online movie store database, which contains six tables.

  • The customer table stores information on all registered customers. The columns are id, first_name, last_name, join_date, and country.
  • The movie table contains records of all movies available in the store. The columns are id, title, release_year, genre, and editor_ranking.
  • The review table stores customer ratings of the movies. The columns are id, rating, customer_id (references the customer table), and movie _id (references the movie table).
  • The single_rental table stores information about movies that were rented for a certain period of time by customers. The columns are id, rental_date, rental_period, platform, customer_id (references the customer table), movie _id (references the movie table), payment_date, and payment_amount.
  • The subscription table stores records for all customers who subscribed to the store. The columns are id, length (in days), start_date, platform, payment_date, payment_amount, and customer_id (references the customer table).
  • The giftcard table contains information about purchased gift cards. The columns are id, amount_worth, customer_id (references the customer table), payment_date, and payment_amount.

Now that we are familiar with the dataset, let’s proceed to the SQL practice exercises.

Exercise 1: Rank Rentals by Price

Exercise:

For each single rental, show the rental_date, the title of the movie rented, its genre, the payment amount, and the rank of the rental in terms of the price paid (the most expensive rental should have rank = 1). The ranking should be created separately for each movie genre. Allow the same rank for multiple rows and allow gaps in numbering.

Solution Show solution

Exercise 2: Find 2nd Giftcard-Purchasing Customer

Exercise:

Show the first and last name of the customer who bought the second most-recent gift card, along with the date when the payment took place. Assume that a unique rank is assigned for each gift card purchase.

Solution Show solution

Exercise 3: Calculate Running Total for Payments

Exercise:

For each single rental, show the id, rental_date, payment_amount and the running total of payment_amounts of all rentals from the oldest one (in terms of rental_date) to the current row.

Solution Show solution

Exercise 4: Calculate Moving Average for Scores

Exercise:

For each procedure, show the following information: procedure_date, doctor_id, category, name, score and the average score from the procedures in the same category which are included in the following window frame: the two previous rows, the current row, and the three following rows in terms of the procedure date.

Solution Show solution

Exercise 5: Find the Difference Between Procedure Prices

Exercise:

For each procedure, show the following information: id, procedure_date, name, price, price of the previous procedure (in terms of the id) and the difference between these two values. Name the last two columns previous_price and difference.

Solution Show solution

Exercise 6: Find the Difference Between the Current and Best Prices

Exercise:

For each procedure, show the:

  • procedure_date
  • name
  • price
  • category
  • score
  • Price of the best procedure (in terms of the score) from the same category (column best_procedure).
  • Difference between this price and the best_procedure (column difference).

Solution Show solution

Exercise 7: Find the Best Doctor per Procedure

Exercise:

Find out which doctor is the best at each procedure. For each procedure, select the procedure name and the first and last name of all doctors who got high scores (higher than or equal to the average score for this procedure). Rank the doctors per procedure in terms of the number of times they performed this procedure. Then, show the best doctors for each procedure, i.e. those having a rank of 1.

Solution Show solution

SQL Window Functions Practice Exercises: Athletic Championships

Dataset

The following exercises use the athletic championships database that contains eight tables.

  • The competition table stores information about competitions. The columns are id, name, start_date, end_date, year, and location.
  • The discipline table stores information for all running disciplines (from the short-distance runs (e.g. the 100 meter) to the long-distance runs (e.g. the marathon)). The columns are id, name, is_men, and distance.
  • The event table stores information about the competition and discipline for each event. The columns are id, competition_id (references the competition table), and discipline_id (reference the discipline table).
  • The round table stores the rounds of each event. The columns are id, event_id (references the event table), round_name, round_number, and is_final.
  • The race table stores data for each race of each round. The columns are id, round_id (references the round table), round_name (same as in the round table), race_number, race_date, is_final (same as in the round table), and wind.
  • The athlete table stores information about athletes participating in the competition. The columns are id, first_name, last_name, nationality_id (references the nationality table), and birth_date.
  • The nationality table stores information about athlete’s countries of origin. The columns are id, country_name, and country_abbr.
  • The result table stores information for all participants of a particular event. The columns are race_id (references the race table), athlete_id (references the athlete table), result, place, is_dsq, is_dns, and is_dnf.

Now that we are familiar with the dataset, let’s proceed to the SQL practice exercises.

Exercise 8: Calculate the Difference Between Daily Wind Speed Averages

Exercise:

For each date in which there was a race, display the race_date, the average wind on this date rounded to three decimal points, and the difference between the average wind speed  on this date and the average wind speed on the date before, also rounded to three decimal points. The columns should be named race_date, avg_wind, and avg_wind_delta.

Solution:Show solution

Exercise 9: Compare the Best and Previous Results

Exercise:

For each woman who ran in the final round of the women's marathon in Rio, display the following information:

  • The place they achieved in the race.
  • Their first name.
  • Their last name.
  • comparison_to_best – The difference between their time and the best time in this final.
  • comparison_to_previous – The difference between their time and the result for the athlete who got the next-highest

Sort the rows by the place column.

Solution:Show solution

Exercise 10: Look Ahead with the LEAD() Function

Exercise:

Take the statistics for the website with id = 2 between 1 and 14 May 2016 and show the day, the number of users, and the number of users 7 days later.

Note that the last 7 rows don't have a value in the last column. This is because no rows '7 days from now' can be found for them. For these cases, show -1 instead of NULL if no LEAD() value is found.

Solution:Show solution

Exercise 11: Look Back with the LAG() Function

Exercise:

Show the statistics for the website with id = 3 that include day, revenue, and the revenue 3 days before. Show -1.00 for rows with no revenue value 3 days before.

Solution:Show solution

More SQL Window Functions Practice

The SQL window functions practice exercises presented in this article provide a comprehensive platform for honing your SQL and data analysis skills one query at a time. These exercises come from our courses; to find additional practice exercises, visit the courses linked below.

  1. Window Functions
  2. Window Functions Practice Set

If you aim to learn about or refresh your knowledge of window functions, we suggest you start with the Window Functions course, which offers a thorough exploration of this topic. For those seeking to hone their skills in window functions, explore our Window Functions Practice Set. It has 100 exercises structured into three distinct parts, each utilizing a different dataset.

Sign up now and get started for free. Good luck!