Window functions entered the SQL:2003 standard, but SQL Server didn't ship a full implementation until 2012. The 2005 release got as far as the ranking quartet (ROW_NUMBER, RANK, DENSE_RANK, NTILE) and then stopped. For seven years, if you wanted LAG, LEAD, a running total, or a frame clause on Microsoft's engine, you wrote ugly correlated subqueries and hoped the optimizer cooperated.
The 2012 release closed most of the gap, borrowing shape directly from Oracle's 8i implementation (2001) and PostgreSQL 8.4 (2009). A few corners of the 2003 standard still aren't supported: the FILTER clause, NULLS FIRST/LAST, and the full RANGE specification. The history explains the workarounds you'll see on Stack Overflow from senior DBAs who lived through both eras.
ANSI adds window funcs
SQL Server adds ranking
LAG, LEAD, frame clause
Oracle 8i, the original
Source: DataDriven analysis of 1,042 verified data engineering interview rounds.
Three categories entered SQL Server across two releases. The 2005 edition brought the ranking quartet borrowed from Oracle 8i. The 2012 edition brought offset and aggregate window functions, completing the SQL:2003 specification that the original shipped without.
| Function | Available | Notes |
|---|---|---|
| ROW_NUMBER() | All versions | Works the same as PostgreSQL. Requires ORDER BY. |
| RANK() | All versions | Gaps after ties. Identical to PostgreSQL. |
| DENSE_RANK() | All versions | No gaps after ties. Identical to PostgreSQL. |
| NTILE(n) | All versions | Splits rows into n groups. Same behavior across engines. |
| Function | Available | Notes |
|---|---|---|
| LAG() | SQL Server 2012+ | Access previous row. Same syntax as PostgreSQL. |
| LEAD() | SQL Server 2012+ | Access next row. Same syntax as PostgreSQL. |
| FIRST_VALUE() | SQL Server 2012+ | First value in the window frame. |
| LAST_VALUE() | SQL Server 2012+ | Last value in frame. Watch for default frame behavior. |
| Function | Available | Notes |
|---|---|---|
| SUM() OVER() | All versions | Running totals. Requires frame clause for cumulative sums. |
| AVG() OVER() | All versions | Moving averages. Frame clause controls the window. |
| COUNT() OVER() | All versions | Running counts. Works with or without PARTITION BY. |
| MIN/MAX() OVER() | All versions | Running min/max within the window. |
These are the specific areas where SQL Server window function behavior differs from PostgreSQL. Knowing these differences prevents syntax errors during interviews and on the job.
PostgreSQL
Supported. SUM(amount) FILTER (WHERE status = 'paid') OVER (...).
SQL Server
Not supported. Use CASE WHEN inside the aggregate: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) OVER (...).
Interview note: If you write a FILTER clause in a SQL Server interview, the interviewer will flag it. Always use CASE WHEN for conditional aggregation in SQL Server.
PostgreSQL
Full support since PostgreSQL 8.4. ROWS BETWEEN, RANGE BETWEEN, GROUPS BETWEEN (PG 11+).
SQL Server
ROWS BETWEEN supported since SQL Server 2012. RANGE BETWEEN has limited support. GROUPS not supported. Before 2012, frame clauses were not available.
Interview note: SQL Server 2012 was the turning point for window function support. If the interview specifies SQL Server 2008 or earlier, you cannot use LAG, LEAD, or custom frame clauses.
PostgreSQL
Supported. ORDER BY col ASC NULLS LAST.
SQL Server
Not supported as syntax. NULLs sort first for ASC and last for DESC by default. Workaround: ORDER BY CASE WHEN col IS NULL THEN 1 ELSE 0 END, col.
Interview note: Know the default NULL ordering for SQL Server (NULLs first in ASC). If the interviewer asks how to push NULLs to the end, use the CASE WHEN workaround.
PostgreSQL
Not supported. Cannot use COUNT(DISTINCT col) OVER ().
SQL Server
Also not supported. Neither engine allows DISTINCT inside window aggregates.
Interview note: This is a trick question. Neither PostgreSQL nor SQL Server supports COUNT(DISTINCT) OVER. The workaround is a subquery or CTE with DENSE_RANK to assign unique identifiers, then COUNT the ranks.
PostgreSQL
STRING_AGG is an aggregate function but cannot be used as a window function with OVER.
SQL Server
STRING_AGG (SQL Server 2017+) also cannot be used with OVER. Neither engine supports windowed string concatenation.
Interview note: If you need per-group string aggregation alongside other window functions, compute it in a separate CTE and join back.
Running totals are the most common window aggregate in interviews. SQL Server requires an explicit frame clause for cumulative sums. Without a frame clause, the default depends on whether ORDER BY is present.
-- Running total in SQL Server (2012+)
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales;
-- Moving 7-day average
SELECT
order_date,
daily_revenue,
AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_day_avg
FROM daily_sales;SQL Server and PostgreSQL differ in default frame behavior when ORDER BY is present in the OVER clause. Both use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as the default. This means tied rows in the ORDER BY column are included in the frame together, which can produce unexpected running totals. Always specify ROWS BETWEEN explicitly to avoid ambiguity.
-- Default frame (RANGE) with ties: may surprise you
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_sum
FROM orders;
-- If two rows have the same order_date,
-- both see the sum INCLUDING the other tied row.
-- Fix: specify ROWS explicitly
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_sum
FROM orders;
-- Each row sees only rows up to and including itselfThese questions specifically target SQL Server knowledge. Each includes the SQL Server-specific considerations that interviewers look for.
What they test:
Basic window function usage in SQL Server. The syntax is identical to PostgreSQL for this pattern. The interviewer verifies you know ROW_NUMBER or RANK in the OVER clause with PARTITION BY.
Approach:
Use ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) in a CTE, then filter WHERE rn <= 3. If the question says 'including ties,' switch to RANK or DENSE_RANK. The syntax is the same as PostgreSQL. No SQL Server-specific gotchas here.
What they test:
Frame clause knowledge. The interviewer wants to see ROWS BETWEEN 29 PRECEDING AND CURRENT ROW (not RANGE). They also want to know that this requires SQL Server 2012 or later. If the target is SQL Server 2008, you need a self-join or subquery approach.
Approach:
AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW). Mention that ROWS is preferred over RANGE to avoid including tied dates. If the interview specifies pre-2012, explain the self-join alternative: join the table to itself where the joined date is within 30 days, then average.
What they test:
Cross-engine knowledge. The interviewer wants to hear about FILTER clause (not supported), NULLS FIRST/LAST (not supported), GROUPS frame type (not supported), and limited RANGE support before 2022. Strong candidates also mention that SQL Server 2008 and earlier had no LAG, LEAD, or frame clauses.
Approach:
List the key gaps: no FILTER clause (use CASE WHEN), no NULLS FIRST/LAST (use CASE WHEN in ORDER BY), no GROUPS frame type, and limited RANGE before 2022. Then mention that ranking functions (ROW_NUMBER, RANK, DENSE_RANK) work identically across both engines, so most interview problems have the same solution regardless of engine.
The patterns Oracle shipped in 2001 are the same patterns you'll write at the whiteboard tomorrow. Learn the lineage and the syntax stops feeling arbitrary.