PL/SQL & T-SQL (Oracle and SQL Server) Module 5
Window functions in T-SQL interview questions
Interview questions on window functions in T-SQL: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.
Warm-up questions
Short questions with short answers, each answered in full.
-
Fresher to mid
How do ROW_NUMBER, RANK and DENSE_RANK number tied rows?
All three number the rows of each partition in the order of the window's ORDER BY, and they differ only on ties. ROW_NUMBER gives every row a number of its own, so two tied rows get, say, 2 and 3, in an order that only a tie-breaker makes repeatable. RANK gives tied rows the same number and then jumps, so the row after a tie at second place is fourth. DENSE_RANK also shares the number but continues with the next one, so that row is third. For the units 90, 20, 20 and 10 they give 1, 2, 3, 4, then 1, 2, 2, 4, then 1, 2, 2, 3.
From the lesson T-SQL ranking and offset window functions
-
Fresher to mid
In which clauses of a T-SQL query can a window function appear?
Only in the SELECT list and in ORDER BY. SQL Server works out a query in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and window functions get their values in the SELECT step, so WHERE, GROUP BY and HAVING cannot use them: a window function in WHERE fails with error 4108. SQL Server also has no QUALIFY clause. To filter on a window result, compute it in a CTE or a derived table and put the condition in the outer query.
From the lesson T-SQL ranking and offset window functions
-
Fresher to mid
How do ROWS and RANGE frames treat tied rows in T-SQL?
A ROWS frame counts physical rows, so in a running total each of two tied rows stops at itself and gets its own total, in the order that the ORDER BY and its tie-breaker give. A RANGE frame works on values: its CURRENT ROW is every row with the same ORDER BY value, so tied rows share one total that includes all of them. RANGE is what T-SQL uses when a window has an ORDER BY and no frame, which is why running totals are written with ROWS and a unique ORDER BY. In T-SQL a RANGE frame accepts only UNBOUNDED and CURRENT ROW as bounds, and there are no GROUPS frames.
From the lesson T-SQL window frames and running totals
Questions with model answers
What each question tests, the first step of a good answer and the follow-ups to expect.
-
Mid to senior
A dashboard refreshed every minute needs the latest status of each order from a table of 40 million status events. One version numbers all events with ROW_NUMBER in a CTE and keeps n = 1; another uses CROSS APPLY with SELECT TOP (1) ordered by updated_at DESC. Compare the two and choose, with the index each needs.
- What it tests
- Reading the cost of top-1-per-group queries: a full numbering pass against one seek per group, and the index that serves each.
- First step
- Both are correct only with a unique order, so add event_id as a tie-breaker first. Then compare the work: the CTE version reads and numbers all 40 million events, while CROSS APPLY reads one row per order from an index on order_id, updated_at DESC, event_id DESC.
- Follow-ups
-
- When is the ROW_NUMBER version the faster one?
- What does the index need for the dashboard's other columns?
- How would you avoid the query altogether?
From the lesson T-SQL ranking and offset window functions
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to staff
A nightly feed has left duplicate customer rows, same email, different load times. A developer wants to run DELETE through a CTE with ROW_NUMBER() OVER (PARTITION BY email ORDER BY loaded_at DESC) WHERE n > 1 on production tonight. What would you check or change before it runs, and how do you make it safe to repeat?
- What it tests
- Turning a textbook de-duplication into a safe production change: ties, NULL keys, collation, references from other tables, a record of what was deleted, and a constraint against new duplicates.
- First step
- Run the CTE as a SELECT first and look at what it would delete. Check three things in that list: rows that tie on loaded_at, customers with a NULL email, which PARTITION BY puts in one group, and emails that differ only in case, which a case-insensitive collation treats as equal.
- Follow-ups
-
- What if orders already point to the rows the DELETE removes?
- How do you stop the duplicates from coming back?
- How would you run it on 50 million rows?
From the lesson T-SQL ranking and offset window functions
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A product team wants each stall's 7-day moving average of daily sales, with days without sales counted as zero. A colleague proposes AVG(amount) OVER (PARTITION BY stall ORDER BY sold_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) on the sales table. What is wrong with it, and how would you write it in T-SQL?
- What it tests
- Knowing that ROWS counts rows and not days, that T-SQL has no RANGE offsets, and building one row per stall and day before the frame is applied.
- First step
- The frame reads the last seven rows, not the last seven days: two sales on one day count as two of the seven, and a day without sales does not count at all. Build one row per stall and day first, from a list of days, so that empty days exist with a total of zero.
- Follow-ups
-
- How do you produce the days without sales?
- What should the average show for the first six days of the data?
- How would Oracle write the same query?
From the lesson T-SQL window frames and running totals
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
Customers earn a badge for paying on consecutive days. Payments can arrive several times a day, some are reversed later, and the badge shows the current streak. Describe the query, and say what breaks the ROW_NUMBER difference trick on this data.
- What it tests
- Applying gaps and islands to real data: one row per customer and day, reversed payments left out, the current streak rather than the longest, and the boundary of a day.
- First step
- The trick needs exactly one row per customer and day, so build that first: keep the payments that were not reversed, turn their time into the customer's local date, and take the distinct days. Only then number the days and subtract the number from the day.
- Follow-ups
-
- Which streak is the current one?
- What if a reversal arrives a day after the payment?
- Which index helps the query?
From the lesson T-SQL window frames and running totals
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track