Where can an SQL professional find a comprehensive guide to advanced SQL interview questions? The shortest answer is: here! We selected the 27 most important SQL questions and answered them for you.

We’ll go through these four main concepts and a few more besides:

• JOINs
• GROUP BY, WHERE, and HAVING
• CTEs (Common Table Expressions) and recursive queries
• Window Functions

The best way to refresh your advanced SQL knowledge is by taking our interactive Advanced SQL track. It has over 300 hands-on exercises for window functions, Common Table Expressions, recursive functions, and more.

Let’s attack these questions frontally, without further ado!

## 1.  What Is a JOIN in SQL?

JOIN is an SQL command that allows you to combine two or more tables. This is done via a common column (i.e. a column that has the same values in both tables), which allows using data from two or more tables at the same time. Joining tables in SQL is essential due to the nature of relational databases: data is atomized into tables, with each table holding only a part of the data available in the database.

We’ll use two tables to showcase how this works. The first table is football_players.

idfirst_namelast_namenational_team_idgames_played
1GianfrancoZola135
2Virgilvan Dijk253
3MarcusRashford351
4KylianMbappé566
5PhilFoden322
6Frenkiede Jong222
7MarioBalotelli136
8ErlingHaaland623

The second is national_team.

idcountry
1Italy
2Netherlands
3England
4Croatia

Here’s a query that joins two tables:

SELECT
fp.id,
first_name,
last_name,
national_team_id,
country,
games_played
FROM football_players fp
JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;


It selects columns from both tables. To join them, we first reference one table in the FROM clause. This is followed by JOIN, and after that comes the second table. We use the ON clause to specify the condition with which the tables will be joined: the national_team_id in the football_players table must be equal to the id column in the national_team table.

The output of the query is:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

INNER JOIN is one of the several distinct joins in SQL. Its characteristic is that it only returns data from the joined tables where the joining condition is true. Here are more details on how the SQL INNER JOIN works

## 2.  What Is the Difference Between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

There are different types of joins in SQL. The most commonly used joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.  LEFT JOIN, RIGHT JOIN, and FULL JOIN are so-called outer joins. JOIN (aka INNER JOIN) is an inner join. In this case, ‘inner’ means that it returns only the rows from both tables that satisfy the joining condition; outer joins return all the rows in one table, plus matching rows in the other table(s). The exception is FULL JOIN, which returns all rows from both tables.

Here’s the result of the INNER JOIN from the previous example. Let’s have it here again. That way, it’ll be easier to see the difference between different joins.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

LEFT JOIN returns all the data from the left table (i.e. the first table, which is listed before\to the left of the JOIN keyword) and only the matching rows from the right table (the second table, listed after\to the right of the JOIN keyword). If there is no matching data in the right table, the missing values are shown as NULLs. Here’s the same query with LEFT JOIN substituting for INNER JOIN:

SELECT
fp.id,
first_name,
last_name,
national_team_id,
country,
games_played
FROM football_players fp
LEFT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;


The left table here is football_players, and the right is national_team. As you’d expect, the output is different:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23

All the football players from the left table are here. However, Kylian Mbappe and Erling Haaland don’t have a matching country in the right table, so there are NULLs in the country column for those players. These rows were not present in the INNER JOIN result. They were added by the LEFT JOIN.

The RIGHT JOIN does the opposite: it returns all the data from the right table and only the matching data from the left table. When there is no matching data in the left table, the missing values are shown as NULLs.

Here’s the code:

SELECT
fp.id,
first_name,
last_name,
national_team_id,
country,
games_played
FROM football_players fp
RIGHT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;


Everything stays the same, except we’re using RIGHT JOIN instead of LEFT JOIN. This is the output:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
NULLNULLNULLNULLCroatiaNULL

We now have all the national teams and their players. But you can see there’s one country (Croatia) that has no players in the left table. The player columns for Croatia are filled with NULLs.

FULL JOIN outputs all the data from all the joined tables. Again, if there is no matching data in the corresponding table, the missing values will appear as NULL.

Once again, we change the join type in the query:

SELECT
fp.id,
first_name,
last_name,
national_team_id,
country,
games_played
FROM football_players fp
FULL JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;


It will return all data from both tables. Any non-matching data is replaced by NULLs. All players are in the result, even if they don’t have a corresponding country in the other table. All countries are in the result, even if they don’t have players in the football_player table. The FULL JOIN result is the union of LEFT JOIN and RIGHT JOIN:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23
NULLNULLNULLNULLCroatiaNULL

You can find more info in the article about different JOIN types. You can also see our SQL JOINs Cheat Sheet for a quick refresher.

## 3.  What Is a CROSS JOIN?

A CROSS JOIN is another join type available in SQL. It returns a Cartesian product. This means that CROSS JOIN will return each row from the first table combined with each row from the second table.

It is not used very often. But if you’re tempted to use it, think twice. Returning all the row combinations might take some time – if the query finishes at all!

As an example, let’s use the tables as in the previous two questions. To write the query, use the CROSS JOIN keyword. Since this is a join type that returns all the row combinations from all tables, there’s no ON clause. Have a look:

SELECT
fp.id,
first_name,
last_name,
national_team_id,
country,
games_played
FROM football_players fp
CROSS JOIN national_team nt;


Here’s the output. All players in the football_players table are listed with all countries in the national_team table.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Italy53
3MarcusRashford3Italy51
4KylianMbappé5Italy66
5PhilFoden3Italy22
6Frenkiede Jong2Italy22
7MarioBalotelli1Italy36
8ErlingHaaland6Italy23
1GianfrancoZola1Netherlands35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3Netherlands51
4KylianMbappé5Netherlands66
5PhilFoden3Netherlands22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Netherlands36
8ErlingHaaland6Netherlands23
1GianfrancoZola1England35
2Virgilvan Dijk2England53
3MarcusRashford3England51
4KylianMbappé5England66
5PhilFoden3England22
6Frenkiede Jong2England22
7MarioBalotelli1England36
8ErlingHaaland6England23
1GianfrancoZola1Croatia35
2Virgilvan Dijk2Croatia53
3MarcusRashford3Croatia51
4KylianMbappé5Croatia66
5PhilFoden3Croatia22
6Frenkiede Jong2Croatia22
7MarioBalotelli1Croatia36
8ErlingHaaland6Croatia23

You can learn more about the CROSS JOIN in this illustrated guide to the SQL CROSS JOIN.

## 4.  What Is a Self-Join in SQL?

As you probably suspect, a self-join occurs when the table is joined with itself. It’s important to note that it’s not a distinct command in SQL: any JOIN type can be used to join a table with itself.

The joining is done like any other JOIN, but this time you’ll reference the same table on both sides of the JOIN keyword. Self-joining is especially useful when a table has a foreign key referencing its primary key. This lets you query hierarchical data, such as family trees or a company’s organizational hierarchy. It’s also helpful when you want to find pairs of values. In the example below, we are looking for players from the same national team:

SELECT
fp1.id,
fp1.first_name,
fp1.last_name,
fp1.national_team_id,
fp2.id AS id_2,
fp2.first_name AS first_name_2,
fp2.last_name AS last_name_2,
fp2.national_team_id as national_team_id_2
FROM football_players fp1
JOIN football_players fp2
ON fp1.id <> fp2.id
AND fp1.national_team_id = fp2.national_team_id;


Self-joining means instead of two tables, you’re specifying the same table twice: once in the FROM clause and once after the JOIN clause. Since you’re using the same table twice, you must use aliases for the tables. Each occurrence of the table should be given a distinct alias (fp1, fp2 in our query) so that it is clear which occurrence of the table we’re referring to.

We’re joining players from the same national team (their national_team_id values are equal). However, we don’t want to list a player with himself, so we exclude the case when fp1.id and fp2.id are equal.

The query’s output is this:

idfirst_namelast_namenational_team_idid_2first_name_2last_name_2national_team_id_2
1GianfrancoZola17MarioBalotelli1
2Virgilvan Dijk26Frenkiede Jong2
3MarcusRashford35PhilFoden3
5PhilFoden33MarcusRashford3
6Frenkiede Jong22Virgilvan Dijk2
7MarioBalotelli11GianfrancoZola1

You can use the national_team_id columns from both tables to confirm that, really, Gianfranco Zola and Mario Balotelli played for the same team.

## 5.  Join Two Tables Using a Two-Column JOIN

You’re given two tables. The first one is employee, which has the following data:

idfirst_namelast_name
1SteveBergman
2SteveJohnson
3SteveKing

The second table is customer, which has the following data:

idfirst_namelast_name
1AnnColeman
2SteveBergman
3SteveYoung
4DonnaWinter
5SteveKing

Your task is to return all the employees that are also the company’s customers. Unfortunately, you can’t use the id column because it’s the employee ID in one table and the customer ID in another. In other words, there’s no single column in one table that references the other.

The solution is to join tables on the first and last name, i.e. to use a two-column JOIN.

The code below will first join the tables on the first name. After that, the keyword AND sets the second joining condition, which is the last name. That way, you’ll get data from both tables where the first name/last name combination is the same. If we used only one of these columns, we could’ve gotten the wrong data because employees and customers can have the same first name but different surnames (or vice versa). Here’s the query:

SELECT
e.first_name,
e.last_name
FROM employee e
JOIN customer c
ON e.first_name = c.first_name
AND e.last_name = c.last_name;


Here’s the code output.:

first_namelast_name
SteveBergman
SteveKing

The result shows that Steve Bergman and Steve King are both the company’s employees and customers.

## 6.  Join Two Tables Using a Non-Equi Join

Up till now, we’ve been using equi-joins: joins where there’s an equality sign in the ON condition. Conversely, the non-equi join is a join that has a non-equality condition in the ON clause.

This time, we have data on mobile users and their data usage. The first table is mobile_user, which shows mobile users and their monthly mobile data limit in MB:

idfirst_namelast_namemobile_data_limit
1MichaelWatson5,000
2NicoleGomez10,000
3SamStone8,000

The second table is data_usage, which shows the user's actual monthly data usage in MB:

idmobile_user_iddata_usedperiod
114,9872022_10
226,8752022_10
3312,5472022_10
415,0372022_11
5211,1112022_11
634,8972022_11

The task is to find all the data where the actual usage was above the monthly limit. We want to see the user’s first and last name, monthly limit, actual data used, and the time period.

The solution is to use the non-equi join, as shown below:

SELECT
first_name,
last_name,
mobile_data_limit,
data_used,
period
FROM mobile_user mu
JOIN data_usage du
ON mu.id = du.mobile_user_id
AND mobile_data_limit < data_used;


The query selects all the required info from two tables. The tables are joined using an INNER JOIN. We first join them where the user ID is the same. Then, we add the second condition after the AND keyword. Here we have a non-equality condition that will get us data where the limit is below the monthly usage.

You can see the result below:

first_namelast_namemobile_data_limitdata_usedperiod
SamStone8,00012,5472022_10
MichaelWatson5,0005,0372022_11
NicoleGomez10,00011,1112022_11

If you’re interested, here are some more non-equi join examples.

## 7.  What Does DISTINCT Do?

DISTINCT’s purpose, in general, is to remove duplicate values. Or, put another way, to show unique values in your query’s output.

Imagine you’re working with this loans table that shows loan IDs and their durations in months.

loan_idloan_duration
10011260
10020560
10020848
100333120
10035748
100398120

Different loans can have the same duration, so you want to extract the list of possible loan durations. You can do this by using DISTINCT:

SELECT DISTINCT loan_duration
FROM loans
ORDER BY loan_duration;


The output shows there are loans with durations of 48, 60, and 120 months:

loan_duration
48
60
120

DISTINCT  can be used in SELECT with one column to show only the unique values of that column, as in the above example. If it’s used in SELECT but with multiple columns, then the output will show the unique combinations of all these columns.

You can also use DISTINCT with aggregate functions. If you do that, your query will eliminate duplicate aggregation results.

You can see examples of these usages in our article talking about the role of DISTINCT in SQL.

## 8.  What Does GROUP BY Do in SQL?

GROUP BY is an SQL clause used for arranging data into groups based on a common value or values. It is most commonly used with aggregate functions; this combination will return aggregated data for every group. However, it’s important to know that using aggregate functions within the GROUP BY clause is not allowed.

The general GROUP BY syntax is:

SELECT
column_1,
column_2,
…,
FROM table_name
WHERE …
GROUP BY column_1, column_2
HAVING …
ORDER BY column_1, column_2;


Suppose there’s the table salaries:

idfirst_namelast_namesalarydepartment
1NicholasPoirot4,798.44IT
2SamanthaWolf5,419.24IT
3StewartJohnsons5,419.24IT
4JackieBiden8,474.54Sales
5MarkHamilton10,574.84Sales
6MarianaCosta9,747.54Sales
7PaulStewart3,498.12Accounting
8MaryRutte4,187.23Accounting
9ThomasSchwarz3,748.55Accounting

We’ll use GROUP BY and AVG() to find the average salary by department:

SELECT
department,
AVG(salary) AS average_salary
FROM salaries
GROUP BY department;


We want to see the departments, so we select this column. To calculate the average salary, we apply the AVG() function to the column salary.

All the columns listed in GROUP BY define the data groups. In our example, the groups are defined by the department column: we calculate the average salary for each department.

Our data grouping and aggregation looks like this:

departmentaverage_salary
Accounting3,811.30
Sales9,598.97
IT5,212.31

The average salary in Accounting is 3,811.30. The average salaries in the other two departments are 9,598.97 and 5,212.31, respectively.

When writing a query, GROUP BY always has to come after WHERE but before the HAVING clause. You can learn more about it in this article about GROUP BY in SQL.

## 9.  How Do You Filter GROUP BY Groups?

Once you get the groups you specified in GROUP BY, sometimes you’ll want to filter them. The clue to doing that lies in the syntax from the previous question. The clause that allows you to filter groups is HAVING.

After the filtering criteria are specified in HAVING, the query will return all the data that satisfies the criteria. All other data will be filtered out.

Here’s how it works on the data from the previous question if we had to show only departments with an average salary below 5.500 dollars.

SELECT
department,
AVG(salary) AS average_salary
FROM salaries
GROUP BY department
HAVING AVG(salary) < 5500;


The code is very similar to the one in the previous question. The difference is the HAVING clause. We use it to filter the results and show only departments with salaries below 5,500.

Here’s what the code returns:

departmentaverage_salary
Accounting3,811.30
IT5,212.31

The department missing from the output is Sales because its average salary is 9,598.97.

## 10.  What’s the Difference Between WHERE and HAVING?

If you know the answers to the previous two questions, you probably know the answer to this question.

The main difference is that WHERE is used for filtering data before it’s grouped. Its position in the SELECT statement shows this: it comes before GROUP BY. Due to its purpose, no aggregate functions are allowed in WHERE.

HAVING, on the contrary, is used to filter data after grouping; that’s why it is used after GROUP BY. Also, HAVING allows conditions that include aggregate functions.

The best way to learn the distinction is to read this article on WHERE vs. HAVING in SQL.

## 11.  What Will the Following Query Attempting to Filter NULLs Return?

You’ll often get this type of question at your advanced SQL interview: you’ll be given a code and have to describe the query's return. While writing and reading SQL code go hand-in-hand, it still feels different when you have to analyze the code someone else wrote.

You have data in the table contributors:

idfirst_namelast_namestart_datetermination_date
1ValeriaBogdanov2022-10-11NULL
2NicholasBertolucci2022-04-072022-11-11
3MathildeBauman2022-05-252022-10-01
4TrevorTrucks2022-01-28NULL
5MariaSzabo2022-03-15NULL

What will this code return?

SELECT
first_name,
last_name,
start_date,
termination_date
FROM contributors
WHERE termination_date != '2022-10-01';


If you answer that it will return all rows except ID = 3, you’re wrong! This is a kind of trick question. When reading the WHERE condition, you could read it as: return all the data where the termination date is different from 2022-10-01. By looking at the table, you would think it’s all rows except one.

It is, but not for SQL! As you can see, there are three rows with NULL values. For SQL, NULL doesn’t equal a value; it’s a non-value. So when you set up the condition in WHERE like that, you will be excluding all dates that are not equal to 2022-10-01 and NULL values.

Here’s the output as proof:

first_namelast_namestart_datetermination_date
NicholasBertolucci2022-04-072022-11-11

## 12.  Write a Query That Finds the Number of Songs by Artist. Use LEFT JOIN and COUNT().

Suppose you’re given two tables: artist and song.

Here’s the artist data:

idartist_name
1Prince
2Jimi Hendrix
3Santana
4Otis Redding
5Lou Rawls

Below is the song data:

idartist_idsong_title
11Purple Rain
22Purple Haze
33Europa
41Cream
51Bambi
61Why You Wanna Treat Me So Bad?
72Spanish Castle Magic
83Taboo
93Incident at Neshabur
103Flor D' Luna

You need to use LEFT JOIN and COUNT() to find all the artists, their IDs, and the number of their songs in the database.

You could be tempted to suggest this solution:

SELECT
a.id,
artist_name,
COUNT(*) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;


Let’s take a look at the output:

1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding1
5Lou Rawls1

The output shows all the artists; that’s OK. However, the number of songs for Otis Redding and Lou Rawls is one, which is wrong! Take a look at the table song, and you’ll see there are no artist IDs equal to 4 or 5.

What went wrong? When using COUNT(*) with LEFT JOIN, the aggregate function will count all the non-matched values (NULLs). That’s why the result showed one song each for Otis Redding and Lou Rawls, even though they have no songs in the table.

COUNT(*) is used to count all the rows. To give a correct answer, you should use COUNT(song_title) instead.

SELECT
a.id,
artist_name,
COUNT(song_title) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;


This code will give you the right output:

1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding0
5Lou Rawls0

The number of songs by Prince, Jimi Hendrix, and Santana stayed the same as in the previous output. However, the number of songs by the other two artists is now zero, and that’s the correct count.

## 13.  What’s the Difference Between JOIN and UNION?

JOIN is an SQL clause used for joining two or more tables. It allows using data from all the joined tables. In other words, columns from all tables are shown next to each other, meaning data is stacked horizontally.

UNION is a set operator used for combining the results of two or more SELECT statements. Data is stacked vertically. One of the requirements when using UNION is there has to be an equal number of columns in all unionized SELECT statements. Also, all the selected columns have to be of the same data type.

## 14.  What’s the Difference Between UNION and UNION ALL?

What they have in common is that both are set operators. Also, both operators are used for the same purpose: merging data from two or more SELECT statements.

The requirements regarding the number of columns and their data type are also the same.

Now, the difference is that UNION returns only unique records. On the other hand, UNION ALL returns all the records, which includes duplicates.

Usually, UNION ALL is faster because it doesn’t sort the result to remove the duplicates. The rule of thumb is to use UNION ALL by default. Use UNION only if you need unique results or you’re absolutely sure your query won’t return duplicate data.

## 15.  What Is a Subquery in SQL?

A subquery is a query written inside another SQL query. The ‘another’ query is called the main query, while a subquery is sometimes also called a nested query.

Subqueries can be used in the SELECT, INSERT, UPDATE, and DELETE statements. They can also be used in clauses like FROM or WHERE, which is the most common use.

Here’s an example. The table is products, and it stores information on product names, quantities, and categories:

idproduct_namequantityproduct_category
1Apple MacBook Air (2020) MGN63N/A Space Gray319Laptop
2Fairphone 4 128GB Green 5G208Mobile phone
3Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver157Desktop
4HP 17-cp0971nd487Laptop
5Huawei P30 Pro - 128GB - Blue148Mobile phone
6Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC514Desktop
7Toshiba Dynabook Satellite Pro E10-S-101 Notebook207Laptop
8Samsung Galaxy S23 5G - 256GB - Phantom Black56Mobile phone
9Intel Compleet PC | Intel Core i7-10700459Desktop

We’ll use a subquery and show the total quantity by product category, but only for the individual products whose quantity is above the average quantity for all products.

Here’s the solution:

SELECT
product_category,
SUM(quantity) AS product_quantity
FROM products
WHERE quantity > (SELECT AVG(quantity)
FROM products)
GROUP BY product_category;


The query selects the product category and sums the quantity using the SUM() aggregate function. There’s a condition in WHERE that says only those individual products with a quantity above the average will be included in the sum. We use the subquery and the AVG() function to get this average.

The query returns two rows:

product_categoryproduct_quantity
Laptop806
Desktop973

One category is missing because it doesn’t satisfy the filtering criteria – mobile phones.

There are different types of subqueries, such as scalar, multiple-row, and correlated queries. You can learn more about them in our article on subquery types.

## 16.  Write a Query to Return Salespersons and Their Monthly Sales Data Above Their Personal Sales Average. Use a Correlated Subquery.

A correlated subquery is a type of subquery that uses values from the outer query. It is checked once for each row the outer query returns, which can slow performance.

However, the question insists on using it, so let’s see the data.

The first table is salesperson:

idfirst_namelast_name
1NinaLee
2CarolinaGreen
3MickJohnson

The other table is sales:

idsalesperson_idmonthly_salesperiod
111,200.472021_10
225,487.222021_10
33700.472021_10
4115,747.542021_11
5216,700.872021_11
5314,322.872021_11
619,745.552021_12
729,600.972021_12
836,749.582021_12

Your task is to use a correlated subquery and return the salesperson’s full name, their monthly sales, and the periods where their sales are above their personal average.

Here’s the solution:

SELECT
first_name,
last_name,
monthly_sales,
period
FROM salesperson sp
JOIN sales s
ON sp.id = s.salesperson_id
WHERE monthly_sales >
(SELECT AVG(monthly_sales)
FROM sales
WHERE salesperson_id = sp.id);


The query selects all the required columns. This data is from both tables, so we joined them.

Now comes the crucial part. To filter data, we use the WHERE clause. The condition says the query should return all data where monthly sales are higher than each salesperson’s average sales. How do we calculate these individual average sales? By using the AVG() function in the subquery that we write in the WHERE clause.

Here’s the output:

first_namelast_namemonthly_salesperiod
NinaLee15,747.542021_11
CarolinaGreen16,700.872021_11
MickJohnson14,322.872021_11
NinaLee9,745.552021_12

## 17.  What Are Window Functions in SQL?

SQL window functions get their name from the fact they are applied to a data window. This window is simply a set of rows related to the current row.

Window functions are initiated by the OVER() clause. Another important clause is PARTITION BY, which defines data partitions within a window frame. When this clause is omitted, the partition is the entire result table. When PARTITION BY is used, you can define one or more columns by which data will be partitioned. You can look at it as GROUP BY for window functions.

Another important clause is ORDER BY. It sorts data within the window. In the context of window functions, this clause gives instructions on the order in which the function will be executed.

## 18.  What’s the Difference Between Window Functions and GROUP BY?

The only similarity they share is that both GROUP BY and window functions can be – and very often are – used with the aggregate functions, and they both work on a set of rows.

However, when using GROUP BY, the output is shown as groups, and you can’t see the individual rows forming the group.

Window functions don’t have such problems. One of their characteristics is that they don’t collapse the individual rows when showing aggregated data. This means it’s possible to show aggregated and non-aggregated data simultaneously.

Window functions are used for more than data aggregation, as you’ll see in the following question. But if you want to know more about the current topic, we have an article that explains window functions vs. GROUP BY.

## 19.  What Window Functions Do You Know?

SQL window functions can be generally divided into four categories:

• Ranking Functions
• Distribution Functions
• Analytic Functions
• Aggregate Functions

The ranking functions are:

• ROW_NUMBER() – Returns a unique number for each row within a partition; tied values have different row numbers.
• RANK() – Ranks data within a partition; tied values have the same rank, and there’s a gap following ties (e.g. 1, 2, 3, 3, 5).
• DENSE_RANK() – Ranks data within a partition; tied values have the same rank and there’s no ranking gap (e.g. 1, 2, 3, 3, 4).

The distribution functions are:

• PERCENT_RANK() – Returns the relative rank within a partition.
• CUME_DIST() – Returns the cumulative distribution within a partition.

The analytic functions are:

• LEAD() – Allows accessing values from a subsequent row in relation to the current row.
• LAG() – Allows accessing values from a previous row in relation to the current row.
• NTILE() – Divides rows within a partition into approximately equal groups.
• FIRST_VALUE() – Allows accessing values from the first row within a partition.
• LAST_VALUE() – Allows accessing values from the last row within a partition.
• NTH_VALUE() – Allows accessing the n-th row within a partition.

Finally, the aggregate functions are:

• AVG() – Returns an average value for the rows in a partition.
• COUNT() – Returns the number of values in the rows in a partition.
• MAX() – Returns the maximum value for the rows in a partition.
• MIN() – Returns the minimum value for the rows in a partition.
• SUM() – Returns the sum value of the rows in a partition.

## 20.  How Do You Create a Ranking in SQL?

The easiest way to rank data in SQL is to use one of three ranking window functions:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

You’re given this dataset named album_sales with the following data:

idartistalbumcopies_sold
1EaglesHotel California42,000,000
2Led ZeppelinLed Zeppelin IV37,000,000
3Shania TwainCome On Over40,000,000
4Fleetwood MacRumours40,000,000
5AC/DCBack in Black50,000,000
6Bee GeesSaturday Night Fever40,000,000
7Michael JacksonThriller70,000,000
8Pink FloydThe Dark Side of the Moon45,000,000
9Whitney HoustonThe Bodyguard45,000,000
10EaglesTheir Greatest Hits (1971-1975)44,000,000

These are the sales of the ten best-selling albums in history. As you can see, the albums are not ranked. That’s what we’ll do here: rank them from best- to worst-selling using window functions.

If you use ROW_NUMBER(), the query will look like this:

SELECT
ROW_NUMBER() OVER (ORDER BY copies_sold DESC) AS rank,
artist,
album,
copies_sold
FROM album_sales;


The syntax is simple. First, you choose the window function. Then you use the mandatory OVER() clause that signals it’s a window function. In ORDER BY, you sort data descendingly. This now means the row numbers will be assigned according to the copies sold from high to low.

Of course, list all other columns you need and reference the table to get the same output:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
4Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
8Fleetwood MacRumours40,000,000
9Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,000,000

As you can see, the albums are ranked from one to ten. Two albums sold 45 million copies. However, they are ranked differently (third and fourth) according to random criteria. The same happens with three albums that sold 40 million copies.

If you use RANK(), the syntax is the same, except you use a different window function:

SELECT
RANK() OVER (ORDER BY copies_sold DESC) AS rank,
artist,
album,
copies_sold
FROM album_sales;


However, the output is different:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
7Fleetwood MacRumours40,000,000
7Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,000,000

You can see that the tied albums are ranked as third (two times). The next non-tie album is ranked fifth. The same happens with the albums ranked seventh.

Let’s see what happens if we use DENSE_RANK():

SELECT
DENSE_RANK() OVER (ORDER BY copies_sold DESC) AS rank,
artist,
album,
copies_sold
FROM album_sales;


Here’s the result:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
4EaglesTheir Greatest Hits (1971-1975)44,000,000
5EaglesHotel California42,000,000
6Shania TwainCome On Over40,000,000
6Fleetwood MacRumours40,000,000
6Bee GeesSaturday Night Fever40,000,000
7Led ZeppelinLed Zeppelin IV37,000,000

The first tied albums are ranked as third, which is the same as in the previous result. But the difference is that the next non-tie rank is fourth – meaning the ranking is not skipped.

The three other tied albums are now ranked sixth, not seventh as before. Also, the highest rank is seventh, not tenth.

As you can see, each method returns different results. You should use the one that best suits your data and what you want to achieve with ranking. To learn more, read the article on ranking rows in SQL.

## 21.  What’s the Difference Between RANK() and DENSE_RANK()?

We already touched on the difference in the previous question. You saw it there in a practical example, and now let’s formulate it to answer this question.

RANK() assigns the same rank to rows with the same values. When it gets to the following non-tied row, it skips the rank by the number of tied ranks.

DENSE_RANK() also gives the same rank to tied values. However, the rank is not skipped when it reaches the following non-tied row. In other words, DENSE_RANK() ranks data sequentially.

## 22.  Find the Top n Rows in SQL Using a Window Function and a CTE.

This is a common question and can be solved in several ways. We will use the window function in a CTE to return the desired result.

The available data is stored in the salary table:

idfirst_namelast_namesalarydepartment
1TimThompson10,524.74Sales
2MartinaHrabal7,895.14Accounting
3SusanTruman15,478.69Sales
4CiroConte8,794.41Accounting
5JorgeDe Lucia7,489.15Sales
6CarmenLopez10,479.15Accounting
7CatherineMolnar8,794.89Sales
8RichardBuchanan12,487.69Accounting
9MarkWong9,784.19Sales
10SilviaKarelias9,748.64Accounting

The task here is to return the top three highest paid employees in each department, together with their salary and department.

The approach is this:

WITH ranking AS (
SELECT
first_name,
last_name,
salary,
department,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM salary
)

SELECT *
FROM ranking
WHERE salary_rank <= 3
ORDER BY department, salary_rank;


The first part of the code is a Common Table Expression, or CTE. It’s initiated using the keyword WITH. The CTE is named ranking. After the AS keyword, we write the CTE definition as a SELECT statement in parentheses.

After selecting all the required columns comes ranking, we use the DENSE_RANK() function. You could use any other ranking window function if you wish.

The syntax is familiar. To get the rankings by department, we need to partition data by that column. Also, we want to rank salaries from high to low. In other words, data in the partition needs to be arranged by salary in descending order.

The second SELECT statement (i.e. the outer query) selects all the columns from the CTE and sets the condition in the WHERE clause for filtering only the top three salaries by the department. Finally, the output is sorted by department and salary rank.

Here’s the result:

first_namelast_namesalarydepartmentsalary_rank
RichardBuchanan12,487.69Accounting1
CarmenLopez10,479.15Accounting2
SilviaKarelias9,748.64Accounting3
SusanTruman15,478.69Sales1
TimThompson10,524.74Sales2
MarkWong9,784.19Sales3

## 23.  Compute the Difference Between Two Rows (Delta) Using Window Functions

This problem is most elegantly solved using the LAG() window function. Remember, this is a function that accesses the previous row’s value.

The example data can be found in the table revenue:

idactual_revenueperiod
18,748,441.222022_07
210,487,444.592022_08
37,481,457.152022_09
47,497,441.892022_10
58,697,415.362022_11
612,497,441.562022_12

You need to show the actual revenue, time period, and monthly difference (delta) between the actual and the previous month.

Here’s how to do it.

SELECT
actual_revenue,
actual_revenue - LAG(actual_revenue) OVER (ORDER BY period ASC) AS monthly_revenue_change,
period
FROM revenue
ORDER BY period;


A delta is calculated by subtracting the previous month from the actual month. That’s exactly what this query does! To get the previous month's revenue, the LAG() function comes in handy. The actual_revenue column is the function’s argument, since we want to access the revenue data from the previous row. As with every window function, there is an OVER() clause. In it, we sorted data by period ascendingly because it’s logical to calculate the delta chronologically.

This is the query’s output:

actual_revenuemonthly_revenue_changeperiod
8,748,441.22NULL2022_07
10,487,444.591,739,003.372022_08
7,481,457.15-3,005,987.442022_09
7,497,441.8915,984.742022_10
8,697,415.361,199,973.472022_11
12,497,441.563,800,026.202022_12

The first shows revenue change as NULL. This is expected because there’s no earlier month to deduct from. In 2022_08, there was a revenue increase of 1,739,003.37 = actual month revenue - the previous month's revenue = 10,487,444.59 - 8,748,441.22.  The same logic applies to all the other results.

You can find similar examples in the article about calculating the difference between two rows in SQL.

## 24.  Use Window Functions to Compute a Running Total

A running or cumulative total is a sum of a number sequence. The running total is updated every time a new value is added to the sequence. Think of monthly revenues: the current month's total revenue will include the current month's and all the previous months’ revenue sum.

The window function that’s perfect for calculating a running total (cumulative sum) is SUM().

Let’s show the approach on the same data as in the previous question. The goal is to calculate the cumulative revenue for all available months in 2022.

Here’s the solution:

SELECT
actual_revenue,
SUM(actual_revenue) OVER (ORDER BY period ASC) AS cumulative_revenue,
period
FROM revenue;


The cumulative sum is the sum of the actual month's revenue and the sum of all the previous months’ revenue. The SUM() window function applies this logic. The function’s argument is actual revenue, because that’s what we’re summing. To get it to sum all the previous revenue and the current revenue, sort the data ascendingly by period. Again, it’s logical to calculate a cumulative total from the earliest to the latest month.

This is what the code returns:

actual_revenuecumulative_revenueperiod
8,748,441.228,748,441.222022_07
10,487,444.5919,235,885.812022_08
7,481,457.1526,717,342.962022_09
7,497,441.8934,214,784.852022_10
8,697,415.3642,912,200.212022_11
12,497,441.5655,409,641.772022_12

The cumulative revenue in the first row is the same as the actual revenue. For the second row, the cumulative is 19,235,885.81 = 8,748,441.22 + 10,487,444.59. In September, the cumulative is 26,717,342.96 =  8,748,441.22 + 10,487,444.59 + 7,481,457.15.

The same logic applies to the rest of the table.

## 25.  Find a Moving Average Using Window Functions

A moving average is used when analyzing a series. You can find it under other names, such as rolling mean, rolling average, or running average. It is an average of the current value and the defined number of preceding values. For example, a 7-day moving average is the average of the current day and the six preceding days.

To show you how to calculate it, we’ll use the eur_usd_rate table:

idexchange_ratedate
11.06662022-12-30
21.06832023-01-02
31.05452023-01-03
41.05992023-01-04
51.06012023-01-05
61.05002023-01-06
61.06962023-01-09
71.07232023-01-10
81.07472023-01-11
91.07722023-01-12
101.08142023-01-13

We’ll calculate the 3-day moving average in the following way:

SELECT
exchange_rate,
AVG(exchange_rate) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS eur_usd_moving_average,
date
FROM eur_usd_rate;


We use the AVG() window function on the exchange_rate column. In the OVER() clause, data is sorted by date in ascending order. Now comes the important part! Remember, we need a 3-day moving average involving the current and two previous rows. We specify that in the BETWEEN clause: we tell the function to include two preceding rows and the current row.

Let’s have a look at the result:

exchange_rateeur_usd_moving_averagedate
1.06661.06662022-12-30
1.06831.06752023-01-02
1.05451.06312023-01-03
1.05991.06092023-01-04
1.06011.05822023-01-05
1.05001.05672023-01-06
1.06961.05992023-01-09
1.07231.06402023-01-10
1.07471.07222023-01-11
1.07721.07472023-01-12
1.08141.07782023-01-13

The first date’s moving average is the same as the exchange rate because: 1.0666/1 = 1.0666. For 2023-01-02, it’s calculated like this: (1.0666 + 1.0683)/2 = 1.0675.

In 2023-01-03, we’ll finally have three dates: (1.0666 + 1.0683 + 1.0545)/3 = 1.0631. This logic applies to all the rest of the dates.

## 26.  What’s the Difference Between ROWS and RANGE?

Both ROWS and RANGE are clauses used for defining a window frame. They limit the data range used in a window function within a partition.

The ROWS clause limits the rows. It’s used for specifying a fixed number of rows preceding and following the current row. The rows’ value is not taken into account.

The RANGE clause limits the data range logically. In other words, it limits data by looking into the preceding and following rows’ values in relation to the current row. It disregards the number of rows.

How do you use them in practice? Read our article on ROWS and RANGE for more details.

## 27.  Use a Recursive Query to Find all Employees Under a Given Manager.

A recursive query is a special type of CTE that references itself until it reaches the end of the recursion. It’s ideal for querying graph data or hierarchical structure.

An example of the latter is the company’s organizational structure, shown in the company_organization table:

employee_idfirst_namelast_namemanager_id
5529JackSimmons5125
5238MariaPopovich5329
5329DanJacobsson5125
5009SimoneGudbois5329
5125AlbertKochNULL
5500JackieCarlin5529
5118SteveNicks5952
5012BonniePresley5952
5952HarryRaitt5529
5444SeanElsam5329

This table shows all the employees and the ID of their direct manager.

The task here is to use recursion and return all Jack Simmons’ direct and indirect subordinates. Also, we’ll add a column that can be used for distinguishing different organizational levels. Here’s the code:

WITH RECURSIVE subordinates AS (
SELECT
employee_id,
first_name,
last_name,
manager_id,
0 AS level
FROM company_organization
WHERE employee_id= 5529

UNION ALL

SELECT
co.employee_id,
co.first_name,
co.last_name,
co.manager_id,
level + 1
FROM company_organization co
JOIN subordinates s
ON co.manager_id = s.employee_id
)

SELECT
s.employee_id,
s.first_name AS employee_first_name,
s.last_name AS employee_last_name,
co.employee_id AS direct_manager_id,
co.first_name AS direct_manager_first_name,
co.last_name AS direct_manager_last_name,
s.level
FROM subordinates s
JOIN company_organization co
ON s.manager_id = co.employee_id
ORDER BY level;


We start the recursion using WITH RECURSIVE. (If you’re working in MS SQL Server, use only WITH.)

The first SELECT in a CTE is called anchor member. In it, we reference the dataset and select all the necessary columns. Also, we create a new column with the value zero and filter data in the WHERE clause. Why use this exact condition in WHERE? Because Jack Simmons’ employee ID is 5529, and we want to show him and his subordinates.

Then comes the UNION ALL, which combines the results of the anchor query and recursive query, i.e. the second SELECT statement.

We want recursion to go all the way down through the organizational structure. In the recursive query, we join the CTE with the company_organization table. We again list all the necessary columns from the latter table. Also, we want to add one organizational level with every recursion.

Finally, we come to the query that uses the CTE. This query serves to get data from both the CTE and the company_organization table. We use the CTE to show the employee data. The other table is used for showing the direct manager's info.

Running the code will get you this result:

employee_idemployee_first_nameemployee_last_namedirect_manager_iddirect_manager_first_namedirect_manager_last_namelevel
5529JackSimmons5125AlbertKoch0
5952HarryRaitt5529JackSimmons1
5500JackieCarlin5529JackSimmons1
5012BonniePresley5952HarryRaitt2
5118SteveNicks5952HarryRaitt2

The above table shows Jack Simmons’s direct manager is Albert Koch. Directly under Simmons, there are Harry Raitt and Jackie Carlin. The indirect subordinates are Bonnie Presley and Steve Nicks. Their direct manager is Harry Raitt.

Some other variations of this task can be found in the article about recursive CTEs.