11 SQL Window Functions Exercises with Solutions

SELECT
  rental_date,
  title,
  genre,
  payment_amount,
  RANK() OVER(PARTITION BY genre ORDER BY payment_amount DESC)
FROM movie
JOIN single_rental
  ON single_rental.movie_id = movie.id;

Solution explanation:

The instruction tells us to show certain information about single rentals and movies. Thus, we join the single_rental table with the movie table on their common column (i.e. the movie_id column).

Next, we need to rank all rentals in terms of the price paid per rental. To do so, we use RANK(). Then, in the OVER() clause, we order the data by the payment_amount column in descending order, so that the most expensive rental has the rank of 1.

As the ranking should be created separately for each movie genre, in the OVER() clause, we partition the data by the genre column.

Why did we choose RANK() instead of DENSE_RANK() or ROW_NUMBER()? The instruction says that the same rank for multiple rows is allowed; hence, we reduce the options to RANK() and DENSE_RANK(). The ROW_NUMBER() function assigns consecutive numbers as ranks to successive rows; it doesn’t allow multiple rows with the same rank.

Gaps in row numbering are allowed, so we need the RANK() function. DENSE_RANK() doesn’t skip any number in a sequence, even if multiple rows have the same rank. The following table presents these ranking functions and how they work given a list of data values:

Check out this article to learn more about different ranking functions.

WITH ranking AS (
  SELECT
    first_name,
    last_name,
    payment_date,
    ROW_NUMBER() OVER(ORDER BY payment_date DESC) AS rank
  FROM customer
  JOIN giftcard
    ON customer.id = giftcard.customer_id
)

SELECT
  first_name,
  last_name,
  payment_date
FROM ranking
WHERE rank = 2;

Solution explanation:

We are going to show information about customers and their gift card purchases, so we need to join the customer table with the giftcard table on their common column, (customer_id).

The instruction says to find the customer who bought the second most-recent gift card. To do that, let's first rank the gift card purchases using the ROW_NUMBER() function; we assume that a unique rank is assigned for each gift card purchase.

The inner SELECT statement selects customer information and the dates of their gift card purchases. Then, we rank the rows using the ROW_NUMBER() function to mark the second most-recent gift card purchase (i.e. the rank value of 2).

This inner SELECT statement is a Common Table Expression (CTE). It is wrapped inside the WITH clause and is named ranking. We select relevant data from this CTE and provide a condition in the WHERE clause to output only the row with rank equal 2.

Why do we need to define a CTE and then query it? Because we cannot use the rank column in the WHERE clause of the inner SELECT. The reason is the order of execution, which is: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT. So the rank column is not yet defined at the time when the WHERE clause of the inner SELECT would be executed.

SELECT
  id,
  rental_date,
  payment_amount,
  SUM(payment_amount) OVER(
    ORDER BY rental_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM single_rental;

Solution explanation:

First, we select information about each single rental from the single_rental table.

Next, we are going to find the running total of payment amounts of all rentals using the SUM() function (which takes the payment_amount column as its argument) with the OVER() clause. Here is an article explaining the details about running total and how to compute it in SQL.

The instruction says to find the running total from the oldest rental date until the current row date. So, in the OVER() clause, we need to order the data by the rental_date column and then define ROWS to be counted in the running total, from the oldest date (BETWEEN UNBOUNDED PRECEDING) until the current date (AND CURRENT ROW).

SQL Window Functions Practice Exercises: Health Clinic

Dataset

The following exercises use a health clinic database that contains two tables.

• The doctor table stores information about doctors. The columns are id, first_name, last_name, and age.
• The procedure table contains information about procedures performed by doctors on patients. The columns are id, procedure_date, doctor_id (references the doctor table), patient_id, category, name, price, and score.

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

SELECT
  procedure_date,
  doctor_id,
  category,
  name,
  score,
  AVG(score) OVER(
    PARTITION BY category
    ORDER BY procedure_date
    ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING)
FROM procedure;

Solution explanation:

We are going to show information for each procedure by selecting from the procedure table.

Then we’ll find the average score from the procedures in the same category. To do that, we use the AVG() function with the score column as its argument. This is followed by the OVER() clause, where we partition the dataset into categories.

Additionally, we must consider only rows included in the following window frame: the two previous rows, the current row, and the three following rows in terms of the procedure date. We define this data frame within the OVER() clause. First, we order the dataset by the procedure_date column so the procedures are listed chronologically. And then we define the rows to be considered for calculating the average score value: two previous rows (BETWEEN 2 PRECEDING) and three following rows (AND 3 FOLLOWING), including the current row.

This is known as a moving average. You can learn more in What a Moving Average Is and How to Compute It in SQL.

SELECT
  id,
  procedure_date,
  name,
  price,
  LAG(price) OVER(ORDER BY id) AS previous_price,
  price - LAG(price) OVER(ORDER BY id) AS difference
FROM procedure;

Solution explanation:

Again, we start by selecting information about each procedure from the procedure table.

The instruction says to show the price of the previous procedure. To do that, we use the LAG() function, which returns the previous row value for its argument (here, for the price column). To ensure that we choose the previous procedure price in terms of the id, we order the dataset by the id column in the OVER() clause. We alias it as previous_price.

Now that we have the price value and the previous price value, we can select the difference between these two values. We simply subtract the LAG() function from the price column and alias it as difference.

Check out How to Calculate the Difference Between Two Rows in SQL to learn more.

SELECT 
  procedure_date, 
  name, 
  price,
  category,
  score, 
  FIRST_VALUE(price) OVER(PARTITION BY category ORDER BY score DESC)
     AS best_procedure,
  price - FIRST_VALUE(price) OVER(PARTITION BY category 
     ORDER BY score DESC) AS difference
FROM procedure;

Solution explanation:

We start by selecting information about each procedure from the procedure table.

The next step is to find the price of the best procedure. We use the FIRST_VALUE() function, which returns the first value in an ordered partition of a result set. To get the price of the best procedure from the same category, we must partition the dataset by the category column. And to get the price of the best procedure in terms of score, we must order the dataset by the score column in descending order. We alias this expression as best_procedure.

Lastly, we find the difference between price and best_procedure by subtracting the FIRST_VALUE() function from the price column.

WITH cte AS (
  SELECT
    name,
    first_name,
    last_name,
    COUNT(*) c,
    RANK() OVER(PARTITION BY name ORDER BY count(*) DESC) AS rank
  FROM procedure p 
  JOIN doctor d
    ON p.doctor_id = d.id
  WHERE score >= (SELECT avg(score) 
                  FROM procedure pl 
                  WHERE pl.name = p.name)
  GROUP BY name, first_name, last_name
)

SELECT 
  name,
  first_name,
  last_name
FROM cte
WHERE rank = 1;

Solution explanation:

First, we select the procedure name and information about doctors, so we join the procedure table with the doctor table on their common column (doctor_id).

We want to select all doctors who got high scores (higher than or equal to the average score for this procedure). To do that, we define the WHERE clause condition for the score column. The score column must store a value equal to or greater than the average score for the current row’s procedure.

Let's rank the doctors per procedure. We'll use the RANK() function with the OVER() clause, where we partition the dataset by the procedure name. Additionally, we must rank in terms of the number of times the doctor performed this procedure. To get the number of times the doctor performed this procedure, we must COUNT(*) while grouping by the procedure name and the first and last name of the doctor (that is, we are grouping by all columns listed in the SELECT statement).

All we’ve done until now is define a Common Table Expression (CTE), which is the inner SELECT statement enclosed by the WITH clause and named cte.

Now we select the relevant columns from this CTE. To get the best doctors for each procedure (those having a rank of 1), we define the WHERE clause with the condition for the rank column.

Why do we need to define a CTE and then query it? Because we cannot use the rank column in the WHERE clause of the inner SELECT. The reason is the order of execution, which is: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT. The rank column has not been defined when the WHERE clause is executed.

SELECT
  race_date,
  ROUND(AVG(wind), 3) AS avg_wind,
  ROUND(AVG(wind) - LAG(AVG(wind)) OVER(ORDER BY race_date), 3) 
     AS avg_wind_delta
FROM race
GROUP BY race_date;

Solution explanation:

We are going to display race information for each race date, so we select data from the race table.

To find the average wind speed on this date rounded to three decimal points, we use the AVG() function with the wind column as its argument. Then, we enclose it within the ROUND() function and round it to three decimal places. Note that we must group by the race_date column, since we use the AVG() aggregate function.

We can get the average wind on the date before by using the LAG() function with the AVG(wind) value as its argument. The OVER() clause defines that we order the entire dataset by the race_date column to have the data rows listed chronologically.

Since we want to see the difference between the average wind speed on this date and the average wind speed on the date before, we subtract LAG(AVG(wind)) from AVG(wind). And to round it to three decimal places, we use the ROUND() function again.

SELECT
  place,
  first_name,
  last_name,
  result - FIRST_VALUE(result) OVER (ORDER BY result) 
     AS comparison_to_best,
  result - LAG(result) OVER(ORDER BY result) 
     AS comparison_to_previous
FROM competition
JOIN event
  ON competition.id = event.competition_id
JOIN discipline
  ON discipline.id = event.discipline_id
JOIN round
  ON event.id = round.event_id
JOIN race
  ON round.id = race.round_id
JOIN result
  ON result.race_id = race.id 
JOIN athlete
  ON athlete.id = result.athlete_id
WHERE competition.name = 'Rio de Janeiro Olympic Games'
  AND discipline.name = 'Women''s Marathon'
  AND round.is_final IS TRUE
ORDER BY place;

Solution explanation:

We are going to use information about competitions, disciplines, rounds, athletes, and results. Therefore, we must join all these tables on their common columns, as mentioned in the dataset introduction.

The instruction says to display information for each woman who ran in the final round of the women's marathon in Rio. We cover it in the WHERE clause that contains the following conditions:

• The competition name must be Rio de Janeiro Olympic Games.
• The discipline name must be Women's Marathon.
• The round must be the final round.

Next we select the place column from the result table and the first_name and last_name columns from the athlete table.

To find the difference between their time and the best time in this final, we use the FIRST_VALUE() function with the result column as its argument. This is followed by the OVER() clause, which orders the dataset by the result column. Then we subtract this FIRST_VALUE() function from the current row result. We alias it as comparison_to_best.

To find the difference between their time and the result for the athlete who got the next-better place, we use the LAG() function with the result column as its argument to get the previous result. Once again, this is followed by the OVER() clause to order the dataset by the result column (to ensure we get the next better result). Then we subtract this LAG() function from the current row result. We alias it as comparison_to_previous.

Finally, we sort the rows by the place column using the ORDER BY clause.

SQL Window Functions Practice Exercises: Website Statistics

Dataset

The following exercises use the website statistics database that contains two tables.

• The website table stores information about websites. The columns are id, name, budget, and opened.
• The statistics table stores statistics for each The columns are website_id (references the website table), day, users, impressions, clicks, and revenue.

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

SELECT
  day,
  users,
  LEAD(users, 7, -1) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 2
  AND day BETWEEN '2016-05-01' AND '2016-05-14';

Solution explanation:

We are going to show the day, the number of users, and the number of users 7 days later. The first two values come from the statistics table – these are the day and users columns. The last value must be calculated using the LEAD() function.

We want to see the users column value after seven days; therefore, we pass the users column as the first argument and the value of 7 as the second argument to the LEAD() function. And to ensure that we show -1 instead of NULL if no LEAD() value is found, we pass the third argument as -1.

The LEAD() function is followed by the OVER() clause. This clause contains the condition to order the dataset by the day column, as the statistics should be ordered chronologically.

To show the statistics for the website with id = 2 between 1 and 14 May 2016, we need to define the relevant conditions in the WHERE clause.

SELECT
  day,
  revenue,
  LAG(revenue, 3, -1.00) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 3;

Solution explanation:

We are going to show the day, revenue, and the revenue 3 days before. The first two values come from the statistics table – these are the day and revenue columns. The last value must be calculated using the LAG() function.

We want to see the revenue column value from three days before the current row; therefore, we pass the revenue column as the first argument and the value of 3 as the second argument to the LAG() function. And to ensure that we show -1.00 for rows with no revenue value 3 days before, we pass the third argument as -1.00.

The LAG() function is followed by the OVER() clause. This contains the condition to order the dataset by the day column, as the statistics should be ordered chronologically.

To show the statistics for the website with id = 3, we need to define a condition in the WHERE clause.