Window Functions in SQL: Answer Ranking, Running Total, Lag and Lead Questions Confidently
Why does a sales dashboard know that a store is ranked 3rd in Mumbai, has crossed ₹10 lakh cumulatively this month, and sold 18% less than yesterday - all on the same row? The answer is usually not a complicated join. It is the quiet power of window functions: SQL that can look around a row without destroying the row.
- Window functions calculate across related rows while keeping each original row visible.
- Core syntax:
function() OVER (PARTITION BY group ORDER BY sequence). PARTITION BYcreates mini-groups;ORDER BYdefines the row sequence inside each mini-group.ROW_NUMBER(),RANK()andDENSE_RANK()answer ranking questions with different tie handling.SUM() OVERcreates running totals; add a frame likeROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWfor clarity.LAG()looks backward andLEAD()looks forward - perfect for day-on-day change, previous order, next renewal or churn gaps.- The biggest interview trap: using
GROUP BYwhen the business asks for row-level detail plus a comparison.
Big Picture: Window Functions Add Context Without Collapsing Rows
Think of every row sitting inside a movable analytical window. SQL first keeps the row, then defines its peer group, sorts that group, chooses the visible frame, and calculates a value for the current row.
Core Explanation: The Four Questions Window Functions Answer
Most placement SQL questions using window functions are not about syntax memory. They test whether you can identify the business question type.
1. The Syntax You Must Be Able to Say
The basic shape is:
window_function() OVER (PARTITION BY column ORDER BY column ROWS BETWEEN ...)
2. Ranking Functions: Same Race, Different Tie Rules
Ranking functions assign position inside an ordered partition. The tie rule is what separates an average answer from a strong answer.
Example: if two sellers tie for rank 2, RANK() gives the next seller rank 4, while DENSE_RANK() gives rank 3. That one sentence often saves a candidate.
3. Running Totals: Cumulative Thinking in SQL
A running total is an aggregate calculated from the start of an ordered window up to the current row. In SQL, make the frame explicit so your intention is unambiguous.
SUM(sales) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Worked Example: Store Sales Running Total and Previous-Day Change
Suppose a store has daily sales as follows:
The query pattern:
SUM(sales) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) gives the cumulative value.
LAG(sales) OVER (ORDER BY sale_date) gives the previous row's sales, so sales - LAG(sales) gives day-on-day change.
4. Lag and Lead: Compare This Row With a Neighbour
LAG(column) fetches a value from a previous row in the ordered window. LEAD(column) fetches a value from a later row. These are used when the business question contains words like previous, next, change, gap, repeat, churn, renewal or return.
Definitions You Should Know Cold
PostgreSQL documentation: “A window function performs a calculation across a set of table rows that are somehow related to the current row.”
Case Study: Meesho Seller Performance Dashboard
Meesho’s marketplace model creates exactly the kind of seller, city, product and order-level questions where window functions become a practical analyst advantage.
Meesho operates in Indian e-commerce with a large base of sellers, value-conscious customers, COD behavior, reverse logistics risk and pincode-level demand variation. For a category manager, the problem is not just “total sales by category.” The sharper question is: which sellers are improving, which products are rising within a city, and where are cancellations or returns moving abnormally?
The strategic move in a marketplace control tower is to keep granular rows - seller, SKU, city, date - and add window-based context beside each row. The primary driver is row-level comparability: the analyst can see the actual seller record and its rank, cumulative sales and previous-period movement together. Supporting drivers are consistent partitions such as city or category, reliable ordering by date or GMV, and careful tie handling for seller leaderboards.
The lesson: window functions are not “advanced SQL for the sake of it.” In a marketplace, they convert raw transactions into operating intelligence while preserving the detail managers need to act.
How AI Changes Window Functions in SQL
AI does not remove the need to understand window functions. It changes how quickly analysts can draft, test and explain them.
- Natural-language to SQL is better, but still needs review. Tools can draft queries like “rank sellers by category and calculate weekly change,” but they often miss tie rules, frames or database-specific syntax.
- AI can generate edge-case test data. Ask for ties, missing dates, duplicate timestamps and null values. These are exactly where ranking and running totals break.
- AI helps explain query intent to business users. A good analyst can convert
LAG()output into plain language: “this seller’s GMV fell versus the previous week.”
Use ChatGPT or Claude like a SQL sparring partner: paste a table schema, ask for three window-function interview questions, write your own SQL first, then ask the tool to test it against tie cases, missing dates and duplicate rows.
Interview Relevance
“You have an orders table with customer_id, order_date and amount. Write a query to show each order, the customer’s running spend, and the previous order amount.”
A strong answer sounds like this: “Because we need every order row plus customer-level context, I’ll use window functions. I’ll partition by customer, order by order date, calculate cumulative spend using SUM() OVER, and previous order amount using LAG().”
If the interviewer says “top 3 customers per city,” ask whether ties should be included. If yes, use RANK() or DENSE_RANK(). If exactly three rows are required, use ROW_NUMBER().
Common Mistake
Using GROUP BY when the question needs row-level detail. It costs candidates because GROUP BY collapses rows, so you lose the actual order, seller or transaction record. One-line fix: if the output needs original rows plus rank, previous value or cumulative value, think OVER (PARTITION BY ... ORDER BY ...).
What to Revise Next
Once window functions click, move to the two SQL areas that interviewers reuse most often around them: