# 11 SQL Window Functions Exercises with Solutions

Table of Contents

- Window Functions in a Nutshell
- SQL Window Functions Practice Exercises: Online Movie Store
- SQL Window Functions Practice Exercises: Health Clinic
- SQL Window Functions Practice Exercises: Athletic Championships
- SQL Window Functions Practice Exercises: Website Statistics
- More SQL Window Functions Practice

*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:

- ROW_NUMBER(), RANK(), DENSE_RANK(), and
`NTILE()`

for ranking data. `SUM()`

,`AVG()`

,`COUNT()`

,`MAX()`

, and`MIN()`

for aggregations.- LEAD() and LAG() for comparing data across rows.
`FIRST_VALUE()`

and`LAST_VALUE()`

for extracting boundary values.

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
table stores information on all registered customers. The columns are`customer`

`id`

,`first_name`

,`last_name`

,`join_date`

, and`country`

. - The
table contains records of all movies available in the store. The columns are`movie`

`id`

,`title`

,`release_year`

,`genre`

, and`editor_ranking`

. - The
table stores customer ratings of the movies. The columns are`review`

`id`

,`rating`

,`customer_id`

(references thetable), and`customer`

`movie _id`

(references thetable).`movie`

- 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 thetable),`customer`

`movie _id`

(references thetable),`movie`

`payment_date`

, and`payment_amount`

. - The
table stores records for all customers who subscribed to the store. The columns are`subscription`

`id`

,`length`

(in days),`start_date`

,`platform`

,`payment_date`

,`payment_amount`

, and`customer_id`

(references thetable).`customer`

- The
table contains information about purchased gift cards. The columns are`giftcard`

`id`

,`amount_worth`

,`customer_id`

(references thetable),`customer`

`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

### Exercise 2: Find 2^{nd} 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

### 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

### 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

### 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

### 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

### 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

## SQL Window Functions Practice Exercises: Athletic Championships

### Dataset

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

- The
table stores information about competitions. The columns are`competition`

`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
table stores information about the competition and discipline for each event. The columns are`event`

`id`

,`competition_id`

(references thetable), and`competition`

`discipline_id`

(reference thetable).`discipline`

- The
table stores the rounds of each event. The columns are`round`

`id`

,`event_id`

(references thetable),`event`

`round_name`

,`round_number`

, and`is_final`

. - The
table stores data for each race of each round. The columns are`race`

`id`

,`round_id`

(references thetable),`round`

`round_name`

(same as in thetable),`round`

`race_number`

,`race_date`

,`is_final`

(same as in thetable), and`round`

`wind`

. - The
table stores information about athletes participating in the competition. The columns are`athlete`

`id`

,`first_name`

,`last_name`

,`nationality_id`

(references thetable), and`nationality`

`birth_date`

. - The
table stores information about athlete’s countries of origin. The columns are`nationality`

`id`

,`country_name`

, and`country_abbr`

. - The
table stores information for all participants of a particular event. The columns are`result`

`race_id`

(references thetable),`race`

`athlete_id`

(references thetable),`athlete`

`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:

### 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:

### 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:

### 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:

## 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.

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!