Window Functions SQL Interview Questions

Window functions are a favourite interview topic because a correct answer needs more than syntax. The interviewer wants to know whether you understand what a window sees, in what order SQL evaluates the parts of a query, and what happens with ties. This set of window function interview questions answers each one with a query run on real data, and points out the trap each question is designed to catch.

The data is the UCI Bike Sharing daily table: rentals per day from Capital Bikeshare in Washington, D.C., across 2011 and 2012, with a weather code from 1 (clear) to 4 (heavy rain or snow) (UCI: Bike Sharing). The queries run in DuckDB from Python, so you can repeat them with nothing but pip install duckdb and the CSV.

import duckdb

db = duckdb.connect()
db.execute("""CREATE TABLE days AS
              SELECT dteday::DATE AS day, yr + 2011 AS year, mnth AS month, weathersit AS weather, cnt AS rentals
              FROM 'day.csv'""")                    # UCI Bike Sharing, daily rentals in Washington D.C., 2011-2012


def ask(title, sql):
    print(f"-- {title}")
    for row in db.execute(sql).fetchall():
        print("  ", *row)


ask("Q1 ROW_NUMBER, RANK, DENSE_RANK: 2012 months by number of rainy or snowy days", """
    SELECT month, wet_days,
           ROW_NUMBER() OVER (ORDER BY wet_days DESC, month) AS row_number,
           RANK()       OVER (ORDER BY wet_days DESC) AS rank,
           DENSE_RANK() OVER (ORDER BY wet_days DESC) AS dense_rank
    FROM (SELECT month, COUNT(*) FILTER (WHERE weather >= 3) AS wet_days FROM days WHERE year = 2012 GROUP BY month)
    ORDER BY wet_days DESC, month LIMIT 6""")

ask("Q2 top 2 days per season in 2012 (rank first, filter after)", """
    WITH seasons AS (
        SELECT day, rentals, CASE WHEN month IN (12, 1, 2) THEN 'winter' WHEN month <= 5 THEN 'spring'
                                  WHEN month <= 8 THEN 'summer' ELSE 'autumn' END AS season
        FROM days WHERE year = 2012),
    ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY season ORDER BY rentals DESC) AS n FROM seasons)
    SELECT season, day, rentals FROM ranked WHERE n <= 2 ORDER BY season, n""")

ask("Q3 running total that restarts each month (first 3 days of two months)", """
    SELECT day, rentals, SUM(rentals) OVER (PARTITION BY year, month ORDER BY day) AS month_to_date
    FROM days WHERE day IN ('2012-05-01', '2012-05-02', '2012-05-03', '2012-06-01', '2012-06-02', '2012-06-03')
    ORDER BY day""")

ask("Q4 the default frame trap: ORDER BY month with one row per day", """
    SELECT DISTINCT month,
           SUM(rentals) OVER (ORDER BY month) AS default_frame,
           SUM(rentals) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
    FROM days WHERE year = 2012 AND month <= 2 AND day IN ('2012-01-01', '2012-01-02', '2012-02-01', '2012-02-02')
    ORDER BY month, rows_frame""")

ask("Q5 7-day moving average, and how many days each average really covers", """
    SELECT day, rentals,
           ROUND(AVG(rentals) OVER w) AS avg_7d, COUNT(*) OVER w AS days_in_window
    FROM days WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
    ORDER BY day LIMIT 3""")

ask("Q6 biggest day-over-day drops with LAG", """
    SELECT day, LAG(rentals) OVER (ORDER BY day) AS previous_day, rentals,
           rentals - LAG(rentals) OVER (ORDER BY day) AS change
    FROM days ORDER BY change LIMIT 4""")

ask("Q7 gaps and islands: longest runs of days above 7,000 rentals", """
    SELECT MIN(day) AS first_day, MAX(day) AS last_day, COUNT(*) AS days
    FROM (SELECT day, day - CAST(ROW_NUMBER() OVER (ORDER BY day) AS INTEGER) AS island
          FROM days WHERE rentals > 7000)
    GROUP BY island ORDER BY days DESC, first_day LIMIT 3""")

ask("Q8 share of the year by weekday, with SUM() OVER ()", """
    SELECT DAYNAME(day) AS weekday, SUM(rentals) AS rentals,
           ROUND(100.0 * SUM(rentals) / SUM(SUM(rentals)) OVER (), 1) AS pct_of_2012
    FROM days WHERE year = 2012 GROUP BY weekday, ISODOW(day) ORDER BY ISODOW(day)""")

print("-- Q9 a window function in WHERE")
try:
    db.execute("SELECT day FROM days WHERE ROW_NUMBER() OVER (ORDER BY rentals DESC) <= 3")
except duckdb.Error as error:
    print("  ", str(error).splitlines()[0])
ask("   the same filter with QUALIFY", "SELECT day, rentals FROM days QUALIFY ROW_NUMBER() OVER (ORDER BY rentals DESC) <= 3 ORDER BY rentals DESC")

-- Q1 ROW_NUMBER, RANK, DENSE_RANK: 2012 months by number of rainy or snowy days
   10 2 1 1 1
   2 1 2 2 2
   4 1 3 2 2
   7 1 4 2 2
   12 1 5 2 2
   1 0 6 6 3
-- Q2 top 2 days per season in 2012 (rank first, filter after)
   autumn 2012-09-15 8714
   autumn 2012-09-29 8555
   spring 2012-03-23 8362
   spring 2012-05-19 8294
   summer 2012-07-25 8173
   summer 2012-06-02 8120
   winter 2012-12-04 6606
   winter 2012-12-03 6234
-- Q3 running total that restarts each month (first 3 days of two months)
   2012-05-01 5740 5740
   2012-05-02 6169 11909
   2012-05-03 6421 18330
   2012-06-01 4127 4127
   2012-06-02 8120 12247
   2012-06-03 7641 19888
-- Q4 the default frame trap: ORDER BY month with one row per day
   1 4245 2294
   1 4245 4245
   2 12585 8824
   2 12585 12585
-- Q5 7-day moving average, and how many days each average really covers
   2011-01-01 985 985.0 1
   2011-01-02 801 893.0 2
   2011-01-03 1349 1045.0 3
-- Q6 biggest day-over-day drops with LAG
   2012-04-22 6624 1027 -5597
   2012-03-24 8362 3372 -4990
   2012-10-07 7965 3510 -4455
   2012-10-29 4459 22 -4437
-- Q7 gaps and islands: longest runs of days above 7,000 rentals
   2012-09-19 2012-09-29 11
   2012-09-09 2012-09-16 8
   2012-10-22 2012-10-27 6
-- Q8 share of the year by weekday, with SUM() OVER ()
   Monday 275282 13.4
   Tuesday 288771 14.1
   Wednesday 303879 14.8
   Thursday 310843 15.2
   Friday 305784 14.9
   Saturday 298064 14.5
   Sunday 266953 13.0
-- Q9 a window function in WHERE
   Binder Error: WHERE clause cannot contain window functions!
--    the same filter with QUALIFY
   2012-09-15 8714
   2012-09-29 8555
   2012-09-22 8395

Q1. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows in a window's order; they differ only on ties. October 2012 had two days of rain or snow, and four months tied with one. ROW_NUMBER gave the tied months 2, 3, 4 and 5; RANK gave them all 2 and then jumped to 6; DENSE_RANK gave them all 2 and continued with 3.

The trap: among ties, ROW_NUMBER picks an order the database is free to choose, so the same query can number tied rows differently from run to run. That is why this query adds month as a tie-breaker. Say this unprompted and you have answered the follow-up before it is asked.

Q2. How do you get the top N rows per group?

Number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY ...), then filter in an outer query. The answer shows the two busiest 2012 days in each season. Choose the function deliberately: ROW_NUMBER returns exactly N rows per group, while RANK returns more than N when there is a tie at the cut-off. Ask the interviewer which they want, because "top 2" is ambiguous when two days share second place.

Q3. How do you calculate a running total that restarts each month?

SUM(...) OVER (PARTITION BY year, month ORDER BY day). The partition restarts the total; the ORDER BY makes it cumulative. On 1 June the month-to-date figure dropped back to that day's 4,127 rentals.

Q4. Why does a running total sometimes jump instead of growing row by row?

This is the question that separates people who have used window functions from people who have read about them. With an ORDER BY and no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and under RANGE "current row" includes every row with the same ORDER BY value, its peers (DuckDB: window functions). Ordered by month with one row per day, both January days got the January total, 4,245, at once. Writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gave the expected 2,294 and then 4,245.

The fix to state: order by something unique, such as the date, or spell out a ROWS frame.

Q5. How would you calculate a 7-day moving average, and what is wrong with the first few rows?

AVG(rentals) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). The trap is the start of the data: the first value is an average of one day, the second of two. Counting the rows in the same window, as the query does, makes that visible, and you can then blank out averages built on fewer than seven days. A second trap: ROWS counts rows, not days, so a missing date silently stretches the window. Mention that, and that a RANGE frame over dates or a calendar table fixes it.

Q6. How do you compare each row with the previous one?

LAG(rentals) OVER (ORDER BY day) returns the previous row's value, so subtracting it gives the day-over-day change. The fourth-largest drop in two years was 29 October 2012, when rentals fell from 4,459 to 22; that was the day Hurricane Sandy came ashore in New Jersey (NOAA: Hurricane Sandy report). The first row has no previous row, so LAG returns NULL there, and the change is NULL too.

Q7. How do you find consecutive runs, the gaps and islands problem?

Filter to the qualifying days, number them in date order, and subtract the row number from the date. Within an unbroken run, the date and the row number rise together, so the difference stays the same; the difference changes at every gap. Grouping by it gives one row per run. The longest run of days above 7,000 rentals lasted 11 days, from 19 to 29 September 2012.

Q8. How do you show each group's share of the total?

Divide by a window over the whole result: SUM(SUM(rentals)) OVER (). The inner SUM is the aggregate for each weekday; the outer SUM with an empty OVER () adds those up across all rows. In 2012, Thursday carried 15.2% of rentals and Sunday 13.0%. Nesting an aggregate inside a window function works because window functions run after grouping.

Q9. Why can't you filter on a window function in WHERE?

Because of evaluation order. WHERE runs before window functions are computed, so they are allowed only in the SELECT list and ORDER BY (PostgreSQL: window functions tutorial). DuckDB rejected the query with a binder error. The portable answer is a subquery or CTE, as in Q2. Some engines, including DuckDB, Snowflake and BigQuery, also accept QUALIFY, which filters on window results the way HAVING filters on aggregates (DuckDB: QUALIFY). Mention it, but write the subquery unless you know the interviewer's database supports QUALIFY.

How to answer in the room

• Say the window out loud: what is partitioned, how it is ordered, which rows the frame contains.
• Ask about ties before choosing between ROW_NUMBER, RANK and DENSE_RANK.
• Check the edges: the first row in a partition, a missing date, a NULL value.
• Know which parts are dialect-specific. FILTER, DAYNAME, ISODOW and QUALIFY used above are not in every database; the window functions themselves are standard.

For a full walk through each pattern on a different dataset, see window functions in SQL for data engineers. For the BigQuery dialect, see BigQuery window functions. Broader practice is in SQL interview questions: top 50 with answers, and business-style problems in scenario-based SQL interview questions.

Window functions are a module of the SQL Server course.

Sources

• Window Functions (DuckDB documentation) — DuckDB
• QUALIFY Clause (DuckDB documentation) — DuckDB
• Window Functions tutorial (PostgreSQL documentation) — PostgreSQL Global Development Group
• Bike Sharing dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository
• Tropical Cyclone Report: Hurricane Sandy (NOAA National Hurricane Center) — NOAA National Hurricane Center