The Ultimate Guide to SQL Window Functions

This is a complete overview of the SQL window functions. Learn everything you need to know about them to excel at your job interview and an actual job.

SQL window functions commonly feature in data scientists' and analysts' day-to-day work. They use them for complex calculations that are not possible with other ‘regular’ SQL functions.

Because of that, interviewers at most companies expect job candidates to use window functions in their SQL code whenever possible.

Before doing that, you need to understand the basics of window functions.

What Are SQL Window Functions?

Formally speaking, window functions use values from multiple rows to calculate values for each row separately. In other words, they perform calculations on the set of rows related to the current row. That set of rows is known as a ‘window’, hence the name ‘window functions’.

This might sound like aggregate functions, but there is one significant difference. Window functions, unlike aggregate functions (SUM(), AVG(), etc.), keep individual rows while displaying calculated values.

There are several important aspects of SQL window functions you need to understand when writing a window function.

Why SQL Window Functions Matter in Interviews

Here’s why: window functions are one of the most common ways interviewers separate junior candidates from senior ones.

Technically, you don’t need window functions. Most SQL problems can be solved by using subqueries, self-joins, and GROUP BY. But with window functions, you solve the same problems more elegantly and in fewer code lines. That’s what interviewers are looking for. Anyone can write working but bloated SQL code that returns the expected result.

But if you know when to use window functions and which ones to use, without the interviewer explicitly asking for them? That shows the interviewer you’ve moved beyond simply memorizing the window function syntax and started thinking in patterns. You know down to the smallest detail what a particular window function does and why it’s suitable for a certain pattern.

The 6 SQL Window Function Patterns You Need for Interviews

I’ll now show you how these work in practice by solving scenario-based interview questions.

Interview Pattern #1: Find the Latest Row per User

The question from Amazon and Etsy tasks you with finding the callers whose first and last calls were to the same person on a given day.

Dataset

There’s a table named caller_history.

As you can see, the grain level is a call.

Solution

1. Finding the first and the last call

The most straightforward window-function approach is to use FIRST_VALUE(). As you probably suspected, that’s a window function that returns the first value – i.e., the first row – within the window. 

I’ll use it to get the first call and also to get the last call, by sorting the window in descending order. 

(You might ask why not use LAST_VALUE() in the latter case. Be patient, we’ll come to that. But trust me when I say that FIRST_VALUE() is your safest option.)

The data is partitioned by caller ID and call date. I apply DATE() because without it, each call would be a separate partition, since they are timestamps. 

WITH first_and_last AS
  (SELECT *,
          FIRST_VALUE(recipient_id) OVER(PARTITION BY caller_id, DATE(date_called) ORDER BY date_called ASC) AS first_call,
          FIRST_VALUE(recipient_id) OVER(PARTITION BY caller_id, DATE (date_called) ORDER BY date_called DESC) AS last_call
FROM caller_history)

Be aware that window functions keep all the individual rows. So, the above SQL CTE would produce rows for every call made on a given day.

Let’s query it with a simple SELECT, so you know what I mean.

WITH first_and_last AS
  (SELECT *,
          FIRST_VALUE(recipient_id) OVER(PARTITION BY caller_id, DATE(date_called) ORDER BY date_called ASC) AS first_call,
          FIRST_VALUE(recipient_id) OVER(PARTITION BY caller_id, DATE (date_called) ORDER BY date_called DESC) AS last_call
FROM caller_history)

SELECT *
FROM first_and_last;

Here’s the output.

2. Finding the first call by the caller

As you can see, we’re still on an analytical level. What we need to do here is find a unique combination of the columns required by the question: caller_id, recipient_id, and date_called. We again use DATE() on the date_called, because that’s also a requirement.

In the WHERE clause, we keep only the callers whose first call was to the same recipient as the last call.

Run the code above to see the output.

The Dangers of Using LAST_VALUE()

The thing is that, when you add ORDER BY to a window function, SQL quietly applies a default frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means that the window is all the rows before the current row + the current row.

That’s fine when you use FIRST_VALUE(); the window frame includes all the preceding rows, so the first value really is the first value of the partition.

But for LAST_VALUE() – with that default window definition – the last value is actually the current row.

That’s where you can easily fail your interview, because you think just replacing FIRST_VALUE() with LAST_VALUE() to find the last call, then sorting the partition in the opposite (ascending) order, will do the trick.

No, it won’t. You can run the code above and see that the solution is incorrect.

If you really insist on using LAST_VALUE() – which I don’t recommend, as it’s easy to forget about the window frame – then you should explicitly define the window frame as UNBOUNDED FOLLOWING`. (More about window frames in the section about Interview Pattern #5.)

WITH first_and_last AS (
    SELECT *,
           FIRST_VALUE(recipient_id) OVER (PARTITION BY caller_id, DATE(date_called) ORDER BY date_called ASC) AS first_call,
           LAST_VALUE(recipient_id) OVER (PARTITION BY caller_id, DATE(date_called) ORDER BY date_called ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_call
    FROM caller_history
)

SELECT DISTINCT caller_id,
                first_call AS recipient,
                DATE(date_called)
FROM first_and_last
WHERE first_call = last_call;

Safer Alternative: ROW_NUMBER()

Another way of solving this problem is to use ROW_NUMBER() instead of FIRST_VALUE().

While FIRST_VALUE() is a more elegant solution, you should also know how to use ROW_NUMBER() in this pattern. And when. Which is when you need the full row, need to handle ties deliberately, or need to operate on the first and last groups as separate datasets.

Here’s the interview question solution rewritten with ROW_NUMBER().

This window function assigns unique integers to each row within a partition. You rank them from the first to the last call, then from the last to the first call.

Then, in separate CTEs, keep only rows where the row number is 1; those are your first and last calls. 

Finally, join those two CTEs on the caller_id, call_date, and recipient_id, and there you have it.

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY caller_id, DATE(date_called)
                              ORDER BY date_called ASC)  AS rn_first,
           ROW_NUMBER() OVER (PARTITION BY caller_id, DATE(date_called)
                              ORDER BY date_called DESC) AS rn_last
    FROM caller_history
),
first_calls AS (
    SELECT caller_id, recipient_id, DATE(date_called) AS call_date
    FROM ranked
    WHERE rn_first = 1
),
last_calls AS (
    SELECT caller_id, recipient_id, DATE(date_called) AS call_date
    FROM ranked
    WHERE rn_last = 1
)
SELECT f.caller_id,
       f.recipient_id,
       f.call_date
FROM first_calls f
JOIN last_calls l
    ON  f.caller_id  = l.caller_id
    AND f.call_date  = l.call_date
    AND f.recipient_id = l.recipient_id;

Interview Pattern #2: Rank Customers, Products, or Events

Ranking is another popular interview pattern and typically calls for using one of these functions.

As for the practical use, let’s solve this interview question from the City of San Francisco.

We need to find the top two highest-paid City employees for each job title, ranked by total pay and benefits. The output should include the job title and the names of the highest- and second-highest-paid employees.

Dataset

We’re working with the table named sf_public_salaries.

Solution

1. Ranking employees on their total pay and benefits

We must use ROW_NUMBER() in the solution. Why? We now know that it assigns unique integers, even if there are rows with the same value, i.e., duplicates. 

OK, but the question never explicitly says which ranking window function to use. Of course it doesn’t; it’s an interview question! Do you think your boss will hold your hand and tell you which function to use? No, ‘course not! You have to decide on your own. 

The key to this interview question is in the expectation that exactly one employee lands in position 1 and exactly one in position 2. Different positions, even if they have the same salary + benefits? That sounds like a job for ROW_NUMBER().

We use it to query employees, their job titles and total benefits, and to rank them from highest to lowest total benefits. The ranking is for each job title separately; that’s why I partitioned the data. 

SELECT employeename,
       jobtitle,
       totalpaybenefits,
       ROW_NUMBER() OVER (PARTITION BY jobtitle ORDER BY totalpaybenefits DESC) AS pos
FROM sf_public_salaries

As a standalone query, it gives you this output. You can see that employees within each job title are ranked separately.

Scroll down a little until you reach firefighters. You’ll see ROW_NUMBER() doing its magic: Gregory B Bovo and Ryan C Crow – those must be real names! – have the same total compensation, but they have different ranks. How does it decide which gets which rank? It’s up to the database engine to decide, meaning ROW_NUMBER() is non-deterministic. Yes, run the query the second time, and you might get different results. For this task, it doesn’t really matter. It seems our engine decided to rank alphabetically when total benefits were the same; that’s perfectly fine.

2. Selecting only employees ranked as 1 and 2

In the next step, we turn the above query into a subquery. The outer query uses it to select only the first- and second-ranked employees, then uses CASE WHEN to assign a separate column to each employee. I named those columns primus and secundum. Why not flex your Latin?

SELECT jobtitle,
       CASE
           WHEN pos = 1 THEN employeename
           ELSE NULL
       END AS primus,
       CASE
           WHEN pos = 2 THEN employeename
           ELSE NULL
       END AS secundum
FROM
   (SELECT employeename,
           jobtitle,
           totalpaybenefits,
           ROW_NUMBER() OVER (PARTITION BY jobtitle ORDER BY totalpaybenefits DESC) AS pos
      FROM sf_public_salaries) one
WHERE pos <= 2

Here’s what the output looks like, so you get the feeling of what we’re doing here. That output is basically it; all the info we need is there. Only, we have to make it more readable, as there’s no need to have duplicate job title rows.

3. Grouping the data and finding the first- and second-ranked employees

Now, the final step: all the previous code becomes another subquery, embedded in an outer query. I use MAX() and GROUP BY to collapse two rows into a single row. Don’t worry, MAX() doesn’t perform any meaningful “maximum” calculation here. It’s just a trick I use to extract the single non-NULL value per group; MIN() would work, too. 

Here’s the whole code.

Run the code above to see the output.

Why Do RANK() & DENSE_RANK() Fail Here?

Let’s go back to our first subquery, the one where all the ranking occurs. I’ve changed it slightly by adding RANK() and DENSE_RANK() and keeping only the firefighter job title (only for educational purposes); that’s where there are employees with the same total salary.

SELECT employeename,
       jobtitle,
       totalpaybenefits,
       ROW_NUMBER() OVER (PARTITION BY jobtitle ORDER BY totalpaybenefits DESC) AS row_number_pos,
       RANK() OVER (PARTITION BY jobtitle ORDER BY totalpaybenefits DESC) AS rank_pos,
       DENSE_RANK() OVER (PARTITION BY jobtitle ORDER BY totalpaybenefits DESC) AS dense_rank_pos   
FROM sf_public_salaries
WHERE jobtitle = 'Firefighter'

Here is what you get.

With RANK(), those two employees with the same total salary will both be ranked as 1. Even if we pick the correct first-ranked employee, this method doesn’t assign a rank 2, so the second_best column in the final code will be empty. That’s, of course, incorrect. 

As for DENSE_RANK(), the first part of the problem is the same; both employees are ranked as 1. However, there is an employee ranked as 2, which is great. But, it’s Ernest E Hayles, which is, again, incorrect.

You see where I’m going with this? Let’s see the final code with either method, and let’s keep only firefighters.

Here’s RANK(). 

SELECT jobtitle,
       MAX(primus) AS best,
       MAX(secundum) AS second_best
FROM
  (SELECT jobtitle,
          CASE
              WHEN pos = 1 THEN employeename
              ELSE NULL
          END AS primus,
          CASE
              WHEN pos = 2 THEN employeename
              ELSE NULL
          END AS secundum
   FROM
     (SELECT employeename,
             jobtitle,
             totalpaybenefits,
             RANK() OVER (PARTITION BY jobtitle
                                ORDER BY totalpaybenefits DESC) AS pos
                                
      FROM sf_public_salaries
      WHERE jobtitle = 'Firefighter') one
   WHERE pos <= 2) two
GROUP BY jobtitle;

As I predicted, the second_best column is empty. This is why using RANK() here would make you fail the interview. 

Here’s the same thing, but with DENSE_RANK().

SELECT jobtitle,
       MAX(primus) AS best,
       MAX(secundum) AS second_best
FROM
  (SELECT jobtitle,
          CASE
              WHEN pos = 1 THEN employeename
              ELSE NULL
          END AS primus,
          CASE
              WHEN pos = 2 THEN employeename
              ELSE NULL
          END AS secundum
   FROM
     (SELECT employeename,
             jobtitle,
             totalpaybenefits,
             DENSE_RANK() OVER (PARTITION BY jobtitle
                                ORDER BY totalpaybenefits DESC) AS pos
                                
      FROM sf_public_salaries
      WHERE jobtitle = 'Firefighter') one
   WHERE pos <= 2) two
GROUP BY jobtitle;

You can see in the output that the best and the second-best employees are incorrect. Gregory B Bovo and Ryan C Crow should be there. Instead, we got Ernest E Hayles. That’s the reason why you can’t use DENSE_RANK(), either. 

Interview Pattern #3: Calculate Running Totals and Cumulative Metrics

Running totals and cumulative metrics in general are calculated using the SUM() window function with the ORDER BY clause in OVER().

I’ll demonstrate this with the interview question from Goldman Sachs and Deloitte.

You’re given one day’s data about trains’ departures and arrivals at one train station. You need to find the minimum number of platforms necessary to accommodate the entire scheduled traffic. All that knowing that one platform can only accommodate one train at a time, i.e., from its arrival until its departure time.

Dataset

There are two tables; the first one is train_arrivals.

The second table is, as expected, train_departures.

Solution

In the CTE, we chain two SELECT statements: the first one marks the train’s arrival as 1, the second one marks its departure as -1. With UNION ALL, we merge the results of both statements into a single dataset.

1. Combine arrivals and departures in one dataset

The result is sorted by time in ascending order and by mark in descending order; in case of ties, arrivals are considered before departures.

WITH timetable AS(
    (SELECT train_id,
            arrival_time AS time,
            1 AS mark
     FROM train_arrivals
     
     UNION ALL 
     
     SELECT train_id,
            departure_time AS time,
            -1 AS mark
     FROM train_departures
     )
    ORDER BY time ASC, mark DESC),

Here’s what the data looks like now.

2. Calculating the cumulative sum

Next, we add another CTE where we calculate the running totals. In this case, it’s not cumulative revenue, but a cumulative number of trains waiting at the station at the same time. This will give us the answer to the interview question.

The cumulative is calculated using the SUM() window function and summing marks (i.e., departures and arrivals) from the earliest to the latest time. This is the core principle of cumulative metrics. You can’t sum just any random time periods one after the other; the time data has to be sorted in ascending order.

In addition, we sort the window by mark in descending order. This is to handle the tie-breaking edge case, when a train arrives, and another train departs at the exact same time.

Let’s clarify this. Here’s what happens in the edge case if we first process the departure.

This says that we need only one platform to accommodate these two trains. But that doesn’t make sense, because the first train hadn’t yet left when the second train should have already arrived. We know that one platform can accommodate only one train.

Therefore, we have to process arrivals first, which will increase the cumulative number of trains like this.

The cumulative sum peaks at two. That’s correct, we need two platforms for those two trains.

So, back to our query. By adding the cumsum CTE…

cumsum AS
  (SELECT *,
          SUM(mark) OVER (ORDER BY time ASC, mark DESC) AS trains_at_same_time
   FROM timetable
   )

…we get this output. It’s an overview of the number of platforms for each train’s departure and arrival.

3. Aggregation and the final output

The only thing remaining is to aggregate that into a single number using MAX() to find the maximum cumulative value, which is – I know this might sound contradictory – the minimum number of platforms necessary to accommodate the entire scheduled traffic.

So, here’s the complete solution.

Here’s the output.

Interview Pattern #4: Compare the Current Row to the Previous or Next Row

For this pattern, the two main window functions you’ll need are LAG() and LEAD().

This question by ESPN is a great example of this pattern in practice.

You need to find how the average male height changed between the Olympics from 1896 to 2016. The output should consist of the Olympics year, the average height, the previous Olympics’ average height, and the height difference between the two years.

The question also states that the average height is 172.73 if there’s no average height for that year.

Dataset

You’re given the olympics_athletes_events table.

Solution

How would you solve this problem? By reading the question’s requirements, you should notice that we’re comparing the current year with the previous year. That should signal to you that LAG() is appropriate.

Let’s approach this systematically. 

The only non-calculated column that’s required in the output is year, so we select it. 

Next, we calculate the average height and round the result to two decimal places. 

Now, for each Olympics, we have to access the previous Olympics’ calculated average height. This is what we want to access, therefore AVG(height)::NUMERIC in LAG(). This accesses the previous row; to ensure that the previous row is actually the previous Olympics, we sorted the data within the window from the earliest to the latest year, i.e., ORDER BY year.

In addition, we use COALESCE() to ensure that the average of 172.73 is imputed in case there’s no height data for that year, and, again, round everything to two decimal places.

The third calculation is a simple subtraction of the second calculation from the first one, i.e., the previous Olympics’ average height from the current Olympics’ average height. 

The final touch includes selecting only male athletes in WHERE whose height is not NULL, grouping by year, and sorting the output by year in ascending order.

Run the code above to see the output.

What About LEAD()?

I won’t spend much time on this. Once you know LAG(), you know LEAD(), and vice versa. 

You know that LEAD() accesses the following row’s value. So, you can rewrite the code above by replacing LAG() with LEAD() and simply reversing the ORDER BY direction in OVER().

SELECT year,
       ROUND(AVG(height)::NUMERIC, 2) AS avg_height,
       ROUND(COALESCE(LEAD(AVG(height)::NUMERIC) OVER (ORDER BY year DESC), 172.73), 2) AS prev_avg_height,
       ROUND(ROUND(AVG(height)::NUMERIC, 2) - COALESCE(LEAD(AVG(height)::NUMERIC) OVER (ORDER BY year DESC), 172.73), 2) AS avg_height_diff
FROM olympics_athletes_events
WHERE sex = 'M'
  AND height IS NOT NULL
GROUP BY year
ORDER BY year;

The Old School Corner: How Data Scientists Wrote This in 2008

Do you think LAG() and LEAD() seem quite complex? If you do, I’ll change your mind in a couple of seconds. 

Before window functions became widely available in major databases, old-school data scientists solved that problem by using a self join.

Here’s what that code would’ve looked like in the olden days, when saber-toothed tigers roamed the Earth and window functions were even further away than a junior analyst’s first promotion. 

WITH agg AS (
    SELECT year, AVG(height) AS avg_height
    FROM olympics_athletes_events
    WHERE sex = 'M' AND height IS NOT NULL
    GROUP BY year
)
SELECT
    curr.year,
    ROUND(curr.avg_height::NUMERIC, 2) AS avg_height,
    ROUND(COALESCE(prev.avg_height::NUMERIC, 172.73), 2) AS prev_avg_height,
    ROUND(ROUND(curr.avg_height::NUMERIC, 2) - ROUND(COALESCE(prev.avg_height::NUMERIC, 172.73), 2), 2) AS avg_height_diff
FROM agg curr
LEFT JOIN agg prev
    ON prev.year = (
        SELECT MAX(year)
        FROM agg
        WHERE year < curr.year
    )
ORDER BY curr.year;

Now, compare this to the LAG() solution. What do you say now? I think you’ll be thankful now for the privilege of living in the age of window functions.

Interview Pattern #5: Rolling Averages and Time-Window Analysis

This pattern is used to smooth out noise, making it great for revealing the underlying trend. You can often find it in analyzing revenue trends, DAU/MAU, and product usage metrics. 

The window function used here is AVG(). Unlike in a simple average, a rolling or moving average doesn’t take into account all data points, but only a specified number of the preceding and/or following data points. Using preceding data points is more common. A centered moving average – the one that uses the current and the following rows – is less common, as those following rows might not exist yet; moving averages are typically used on live or recent data.  

You could calculate the 3-day, 7-day, 30-day, 12-month averages, or whatever variation you desire.

For example, a 7-day rolling average includes the 6 previous days + the current day = 7 days. You calculate like that for each day. As you go, the size of the calculation window stays the same (7 days); it just moves day by day.

We’re Going Advanced: The Frame Clause in Window Functions

To calculate rolling averages and perform a time window analysis in window functions, you need to define the frame clause.

The generic syntax is given below. 

<window_function>() OVER (
    [PARTITION BY column_name]
    [ORDER BY column_name]
    [ROWS | RANGE BETWEEN <frame_start> AND <frame_end>]
)

Here are the components of the frame clause.

All these components can be combined in many different ways, which, in turn, results in different frame definitions. Here are the most common combinations.

Now, let’s demonstrate this by solving one of the Amazon SQL interview questions.

We have to calculate a 3-month rolling average of the revenue from purchases. The negative purchase values – i.e., the returns – shouldn’t be included in the calculation. The output should show YYYY-MM and the 3-month rolling average, sorted from the earliest to the latest month.

Dataset 

There’s one table named amazon_purchases.

Solution

Let me first explain the subquery, then we’ll finish it off with the rolling average calculation.

1. Purchase sum by month

The subquery’s purpose is to sum all the purchases by month. We do that by using TO_CHAR to convert the created_at column to the YYYY-MM format, then applying SUM() to calculate monthly revenue. 

The output is grouped by and sorted by month. 

SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
       SUM(purchase_amt) AS monthly_revenue
FROM amazon_purchases
WHERE purchase_amt>0
GROUP BY TO_CHAR(created_at::DATE, 'YYYY-MM')
ORDER BY TO_CHAR(created_at::DATE, 'YYYY-MM')

Here’s its output. 

2. Calculate the moving average

Now, we embed it into the outer query. The outer query calculates the moving average on the data provided by the subquery. The window function used is – expectedly – AVG(), with the window defined as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. This window definition means that the current row and the two before it are included in the calculation. Each row is one month; in other words, we calculate the moving average on the current and the two previous months. 

Of course, the calculation has to run from the earliest to the latest month to make sense, so we sort the data in the window by month in ascending order in the OVER() clause. 

Interview Pattern #6: Retention, Reactivation, and Session Patterns

This is probably the hardest pattern. Not because of any especially difficult window functions, but because it requires chaining multiple window functions.

This pattern encompasses three distinct problem types.

All three are instances of the classic islands-and-gaps problem.

Streak and session detection identify the periods of consecutive activity, i.e., the islands.

Reactivation focuses on gaps, i.e., detecting when a user’s silence exceeds a threshold before a new island begins.

This technique works in three steps.

Let’s now apply this technique to an actual business problem.

Here’s an interview question from LinkedIn and Meta SQL interview questions. 

We need to find the top three users with the longest platform visit streaks up to 10 August 2022. The output must show the user ID and the streak length in days.

Dataset

We’ll use the user_streaks table.

Solution

The solution chains several CTEs.

1. unique_visits CTE: Uses DISTINCT to find unique daily visits per user up to '2022-08-10'.

WITH unique_visits AS (
    SELECT DISTINCT user_id,
                    date_visited
    FROM user_streaks
    WHERE date_visited <= DATE '2022-08-10'
),

2. streak_flags CTE: In this CTE, we reference the previous CTE and use CASE WHEN to mark the new streak with 1. We calculate it by first using LAG() to access the date of the previous visit, then subtracting that date from the current session date. If the difference is 1 – one day – that’s a continuation of the streak, so we mark it as 0. Otherwise, it’s marked as 1.

streak_flags AS (
    SELECT *,
           CASE
               WHEN date_visited - LAG(date_visited) OVER (PARTITION BY user_id ORDER BY date_visited) = 1
               THEN 0
               ELSE 1
           END AS new_streak
    FROM unique_visits
),

3. streak_ids CTE: Here, we use another window function, namely SUM(), to calculate the cumulative sum of the streaks by user. The cumulative sum becomes a streak ID, assigned to each user’s date of visit. That way, we can identify the number of streaks and the number of visits within each streak.

streak_ids AS (
    SELECT *,
           SUM(new_streak) OVER (PARTITION BY user_id ORDER BY date_visited) AS streak_id
    FROM streak_flags
),

4. streak_lengths CTE: Now, we use the info from the previous CTE to COUNT(*) each user’s each streak’s length.

streak_lengths AS (
    SELECT user_id,
           streak_id,
           COUNT(*) AS streak_length
    FROM streak_ids
    GROUP BY user_id, streak_id
),

5. longest_per_user CTE: As a next step, we use MAX() to find the longest streak per user. 

longest_per_user AS (
    SELECT user_id,
           MAX(streak_length) AS streak_length
    FROM streak_lengths
    GROUP BY user_id
),

6. ranked_lengths CTE: Now, we use DENSE_RANK() to rank each user’s streaks by their length, from the longest to shortest.

ranked_lengths AS (
    SELECT DISTINCT streak_length,
           DENSE_RANK() OVER (ORDER BY streak_length DESC) AS len_rank
    FROM longest_per_user
),

7. top_lengths CTE: In this final CTE, we simply keep only the three top longest streaks. 

top_lengths AS (
    SELECT streak_length
    FROM ranked_lengths
    WHERE len_rank <= 3
)

Now, in the final step, everything comes together. We join the longest_per_user and top_lengths CTEs to connect the three longest streaks to their users, and show it as the output. Here’s the complete code. 

Why the Interviewers Love This Pattern

To apply this pattern, you first need to recognize that the problem can’t be solved in one pass. That’s already a plus in the interviewer's notebook. Then, you demonstrate thinking in layers, as I showed you above: detecting, grouping, counting, ranking – each CTE has a clear, distinct purpose.

That way of thinking is what distinguishes candidates who understand data (and the problem) from those who just know syntax.

Common SQL Window Function Mistakes in Interviews

There are several typical window function mistakes candidates make in interviews. Often, these mistakes are silent: the query runs, it returns results, but nothing tells you the result is wrong.

Practice SQL Window Function Interview Questions

The time has come for you to stop reading and start actively practicing what this article has taught you. Here are several hand-picked StrataScratch questions for each window function pattern.

1. Practice: Finding the Latest Row per User

Question #1: Finding Updated Records

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #2: Streamer Sessions by Initial Viewers

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #3: Highest Salary in the Department

Dataset

Solution

Write, run, and check your solution in the code editor. 

2. Practice: Ranking Customers, Products, or Events

Question #1: Ranking Most Active Guests

Dataset

Solution

Write, run, and check your solution in the code editor.

Question #2: Best Selling Item

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #3: Rank Variance per Country 

Dataset

Solution

Write, run, and check your solution in the code editor. 

3. Practice: Calculating Running Totals & Cumulative Metrics

Question #1: Maximum Number of Employees Reached

Dataset

Solution

Write, run, and check your solution in the code editor.

Question #2: Customer Tracking

Dataset

Solution

Write, run, and check your solution in the code editor. 

4. Practice: Comparing Current Row to Previous or Next Row

Question #1: Monthly Percentage Difference

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #2: Growth of Airbnb

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #3: Finding User Purchases

Dataset

Solution

Write, run, and check your solution in the code editor. 

5. Practice: Rolling Averages and Time-Window Analysis

Question #1: Distance per Dollar

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #2: Product Engagement Momentum Shifts

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #3: Search Click Success Rate by User Segment

Dataset

Solution

Write, run, and check your solution in the code editor. 

6. Practice: Retention, Reactivation, and Session Patterns

Question #1: Consecutive Days

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #2: Users by Average Session Time

Dataset

Solution

Write, run, and check your solution in the code editor. 

Question #3: First Day Retention Rate

Dataset

Solution

Write, run, and check your solution in the code editor.

Final Takeaway

This guide gave you a framework for recognizing which problem calls for which window function. I’ve shown you six patterns that cover the vast majority of SQL interview questions where you’ll encounter window functions.

Let’s recap them.

The best way to build a real understanding of these patterns is to work through the questions. Write the code yourself, get it wrong, understand why, and fix it. This is the only way to stop thinking about the window function syntax and start thinking about business problem-solving. It sets you on the path to an interview and job success.

FAQs

1. What is the difference between GROUP BY and a window function?

GROUP BY collapses individual rows and returns one row per group. Window functions don’t do that; they perform a calculation along the individual rows, show the result, and keep the individual rows. 

2. Which SQL window functions are most common in interviews?

• ROW_NUMBER(): Probably the most common one. Used in deduplication, finding the first or last row per group, and pivoting ranked results into columns. 
• RANK() & DENSE_RANK(): They show up in top-N problems. 
• LAG() & LEAD(): We use them for row-to-row comparisons, such as month-over-month change, time between events, and comparing a value to the previous period. 
• SUM() OVER, AVG() OVER, COUNT() OVER: These are aggregate window functions. Used to calculate group-level metrics at the row level, most commonly running totals and moving averages. 
• FIRST_VALUE() & LAST_VALUE(): Used to pull a value from the first or the last row of a partition. They are a common alternative to ROW_NUMBER() when you only need one column, not the whole row

3. When should I use ROW_NUMBER() vs RANK()?

The difference only matters when there are ties, i.e., two or more rows with the same value in the ORDER BY clause in OVER(). 

ROW_NUMBER() always assigns a unique integer to every row. So, it ignores ties and breaks them nondeterministically. You should use this window function whenever the logic requires WHERE rank = 1, i.e., returning a single row. Common examples are deduplication and pivoting positions into columns. 

RANK() should be used when tied rows should share a position and all should appear in the results, because this function assigns the same rank to tied rows, then skips the next rank. 

DENSE_RANK() is a third option, which assigns the same rank to tied rows – just like RANK() – but without skipping the next rank. Use it when you want the Nth-highest value always to be ranked as N, regardless of how many rows share rank N-1. 

4. How do I calculate a running total in SQL?

By using SUM() as a window function with ORDER BY inside OVER().

SELECT date,
       revenue,
       SUM(revenue) OVER (ORDER BY date) AS running_total
FROM daily_revenue;

Such use of the ORDER BY clause activates a default frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means the sum includes all rows from the beginning of the result up to and including the current row. This is a textbook definition of a running or cumulative total.

You can, of course, do the same for each group by using the PARTITION BY clause. In the example below, running totals are calculated per user.

SELECT date,
       revenue,
       SUM(revenue) OVER (PARTITION BY user_id ORDER BY date)
FROM daily_revenue;

Note that ORDER BY in OVER() is what makes the SUM() window function calculate cumulatives. If you use the SUM() OVER() version, that gives the same grand total in every row. 

5. What do LAG() and LEAD() do?

LAG() lets you access the value from the previous row on N rows, ordered by the ORDER BY clause in OVER().

LEAD() does the same, only for the next row on N rows.

6. How do rolling windows work in SQL?

Rolling windows within window functions limit the calculation to a fixed number of preceding and or following rows, rather than all rows from the start. 

The window frame clause has two parts. The first part is the unit, i.e., ROWS or RANGE . ROWS counts physical rows, RANGE determines the included number of rows by value.

The second part of the window frame clause is the bounds BETWEEN … AND …, which define how far the window extends in each direction from the current window. Each bound endpoint – a start or an end – can be one of these five values:

• UNBOUNDED PRECEDING – the first row of the partition
• N PRECEDING – N rows before the current row
• CURRENT ROW – the current row itself
• N FOLLOWING – N rows after the current row
• UNBOUNDED FOLLOWING – the last row of the partition

For example, a rolling window defined as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the window function performs a calculation on 2 rows preceding the current row and the current row, which makes it a 3-row rolling window.