26 Window Functions questions with answers for sql roles — 7 easy, 14 medium, 5 hard.
About Window Functions
Window functions are the most important advanced SQL topic in analyst and data engineer interviews. Master ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, moving averages, NTILE and frames with ROWS and RANGE. Solve problems such as top N per group, month-over-month growth, consecutive days and retention cohorts.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
Window functions perform calculations across a set of rows related to the current row without collapsing them into one row like GROUP BY. They use the OVER clause with optional PARTITION BY, ORDER BY and a frame. Examples are ROW_NUMBER, RANK, running SUM, LAG and LEAD. You can show each order alongside the customer's total, which GROUP BY alone cannot do.
02
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
For salaries 900, 800, 800, 700 ordered descending: ROW_NUMBER gives 1, 2, 3, 4 with unique numbers even for ties; RANK gives 1, 2, 2, 4, skipping after ties; DENSE_RANK gives 1, 2, 2, 3 without gaps. Use ROW_NUMBER for deduplication or picking one row, and DENSE_RANK for Nth highest value questions where ties should share a position.
PARTITION BY divides rows into groups and the window function restarts for each group, like per department. ORDER BY defines the order of rows within each partition, needed for ranking, running totals and LAG or LEAD. Without PARTITION BY the whole result is one partition. Without ORDER BY, aggregate windows compute over the entire partition.
04
What is the difference between GROUP BY and window functions?
GROUP BY collapses rows into one row per group, so detail columns are lost. Window functions keep every row and add the computed value alongside, such as each employee with their department average. You can combine both: group first, then apply a window over the grouped result, for example percent of total using SUM(SUM(sales)) OVER ().
SELECT emp_id, name, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_rank
FROM employees;
PARTITION BY restarts ranking for each department, and DENSE_RANK keeps equal salaries at the same rank.
SELECT sale_date, amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales
ORDER BY sale_date;
Add PARTITION BY store_id to restart the running total for each store.
07
Describe your SQL project from college or internship.
Situation: during my internship the team tracked student attendance in spreadsheets. Task: build a database and reports. Action: I designed tables for students, courses and attendance, loaded data, and wrote queries with joins, group by and window functions to show monthly attendance and low-attendance alerts. Result: faculty got a weekly report automatically, and I learned how real data quality issues affect queries.
LAG returns a value from a previous row in the partition and LEAD from a following row, based on the ORDER BY. LAG(revenue, 1, 0) OVER (ORDER BY month) gives last month's revenue with 0 as default for the first row. They are used for month-over-month change, time between events, and detecting status changes without self joins.
09
What is a window frame and what is the default frame?
A frame defines which rows around the current row are included, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average. When ORDER BY is present and no frame is specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total but includes all ties of the current value. Without ORDER BY, the frame is the whole partition.
10
What is the difference between ROWS and RANGE frames?
ROWS counts physical rows, so 2 PRECEDING means exactly two rows before. RANGE works on values of the ORDER BY column, so rows with the same value are treated together, and value ranges like INTERVAL '7 days' PRECEDING are possible in some databases. With duplicate dates, a RANGE running total jumps for all ties at once, while ROWS increases row by row.
FIRST_VALUE returns the first value in the window frame, LAST_VALUE the last, and NTH_VALUE the nth. A common trap is LAST_VALUE with the default frame, which ends at the current row, so it returns the current row's value. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value of the partition.
NTILE(n) splits ordered rows into n roughly equal buckets, useful for quartiles or deciles. PERCENT_RANK returns (rank - 1) / (rows - 1), a relative position between 0 and 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. They are used for customer segmentation, percentile reports and scoring.
13
Get the top 2 highest paid employees in each department.
WITH r AS (
SELECT emp_id, name, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT * FROM r WHERE rn <= 2;
Use DENSE_RANK instead of ROW_NUMBER if tied salaries should all be included.
WITH m AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct
FROM m ORDER BY month;
NULLIF avoids division by zero.
15
Calculate a 7-day moving average of daily active users.
SELECT activity_date, dau,
ROUND(AVG(dau) OVER (
ORDER BY activity_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 1) AS dau_7d_avg
FROM daily_users
ORDER BY activity_date;
This assumes one row per day; fill missing dates with a calendar table first, or ROWS will span more than seven days.
16
Your running total jumps by large amounts on some dates instead of row by row. Why?
With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.
17
You need each customer's latest address from a history table. How do you query it?
Use ROW_NUMBER partitioned by customer and ordered by updated date descending, then keep rn = 1: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM address_history) SELECT * FROM r WHERE rn = 1. Add a tiebreaker such as id DESC in case two updates share the same timestamp.
18
An interviewer asks to show each employee's salary next to their department's average. How do you do it?
Use a window aggregate: SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff FROM employees. Unlike GROUP BY, every employee row is kept. Mention that a join with a grouped subquery produces the same result but is longer.
19
Tell me about a time you used advanced SQL to solve a business problem.
Situation: the product team wanted to know where users dropped off in the onboarding flow. Task: build a funnel from raw event logs. Action: I used window functions to order events per user, LEAD to find the next step and time taken, and grouped the results into a funnel with conversion rates. Result: we found a 40 percent drop at KYC upload, and the redesign improved completion by 15 percent.
20
Can you use window functions with aggregate functions in the same query?
Yes. Window functions are evaluated after GROUP BY, so they can operate on aggregated values. For example, SELECT region, SUM(sales) AS s, RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk FROM orders GROUP BY region ranks regions by total sales. The aggregate is computed per group first, and then the window function runs over the grouped rows.
WITH spend AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total,
NTILE(4) OVER (ORDER BY total DESC) AS quartile
FROM spend;
Quartile 1 contains the highest spenders. Use this for targeted offers or loyalty tiers.
22
Why can't you use a window function in WHERE, and how do you filter on it?
Window functions are evaluated after WHERE, GROUP BY and HAVING in the logical order, as part of SELECT, so their results do not exist when WHERE runs. To filter, compute the window function in a subquery or CTE and filter in the outer query. Some databases such as Snowflake and BigQuery support a QUALIFY clause for filtering window results directly.
23
What are the performance considerations of window functions?
Each distinct PARTITION BY and ORDER BY combination usually needs a sort, which is expensive on large data and may spill to disk. Reuse the same window definition where possible, using a WINDOW clause, and create indexes that match the partition and order columns so sorts can be avoided. Filter rows before applying windows and avoid unnecessary large frames.
24
Find users who logged in on 3 or more consecutive days.
WITH d AS (
SELECT DISTINCT user_id, login_date FROM logins
), g AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
FROM d
)
SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS days
FROM g GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
Consecutive dates minus row number give the same group value (PostgreSQL date arithmetic).
25
Calculate the time each user spent between consecutive events.
SELECT user_id, event_time, event_name,
event_time - LAG(event_time) OVER (
PARTITION BY user_id ORDER BY event_time
) AS gap_since_prev
FROM events
ORDER BY user_id, event_time;
Gaps above 30 minutes can start a new session using a SUM of a flag over the same window, a common sessionisation pattern.
26
A retention report needs the percentage of users active in each month after signup. How would you build it in SQL?
Compute each user's signup month with MIN over their activity, then derive month number as the difference between activity month and signup month. Group by cohort month and month number, counting distinct users. Divide by the cohort size using FIRST_VALUE or a window SUM over month zero within each cohort. Validate with a few known users and handle users with no activity.
Frequently asked questions
How important is Window Functions in SQL interviews?
Window functions are the most important advanced SQL topic in analyst and data engineer interviews. Master ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, moving averages, NTILE and frames with ROWS and RANGE. Solve problems such as top N per group, month-over-month growth, consecutive days and retention cohorts.
How should I use the Window Functions sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.