Why SQL Window Functions Have Become the #1 Interview Litmus Test for Data Analysts

A few years ago, you could get away with basic SQL in a data analyst interview — a solid GROUP BY here, a decent JOIN there, and you were through. That era is over. In 2026, hiring managers at companies like Meesho, PhonePe, Zomato, and Flipkart have explicitly shifted their SQL rounds to focus heavily on window functions because they separate candidates who truly understand data from those who just know syntax.

Window functions allow you to perform calculations across a set of table rows that are related to the current row — without collapsing the result into a single aggregated output the way GROUP BY does. Think of it this way: if GROUP BY is a summary report, window functions are a live analytical dashboard. You get the detail AND the aggregation, simultaneously.

Why does this matter so much for analysts? Because real business problems demand it. When Swiggy’s data team wants to rank delivery partners by revenue within each city, or when Paytm needs to calculate a 7-day rolling average of transactions per user without losing individual transaction rows, window functions are the tool. These are not textbook problems — they are the exact queries being written in production every single day.

Interviewers know this. That’s why questions on ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and NTILE() are now standard fare in first and second round technical interviews. Candidates who can write these fluently, explain the logic clearly, and debug edge cases on a whiteboard are the ones getting offers. Everyone else is getting a polite “we’ll be in touch” email.

The good news? Window functions follow a consistent mental model once you crack it. And that’s exactly what we’re going to do right here.

The Most Asked SQL Window Function Interview Questions at Top Indian and Global Tech Companies

Based on feedback from 800+ candidates I’ve personally mentored — many of whom have interviewed at Flipkart, Amazon India, Razorpay, CRED, Groww, and Infosys — here are the window function questions that come up again and again. These aren’t guesses. These are real questions that real hiring managers are asking right now in 2026. Study these, practice writing the SQL from scratch, and make sure you can explain your logic out loud.

1. “Write a query to find the second highest salary in each department without using a subquery or LIMIT.” — This classic question is specifically designed to test whether you know DENSE_RANK() and how it handles ties differently from RANK(). Companies like Infosys, TCS Digital, and Wipro Analytics use this to filter candidates immediately. The expected answer uses DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) and then filters WHERE dense_rank = 2.
2. “How would you calculate a 7-day rolling average of daily active users in SQL? Write the full query.” — This is a Zomato, Swiggy, and Paytm favourite because rolling metrics are bread-and-butter in product analytics. It tests your understanding of ROWS BETWEEN in the window frame clause — specifically SUM() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Candidates who don’t know window frames get stuck here and it costs them the round.
3. “Given a table of customer transactions, identify customers who made a purchase this month but not in the previous month. Use window functions.” — This question, frequently seen at CRED, Groww, and Razorpay, is testing LAG() or LEAD() functions combined with conditional logic. It also evaluates whether you understand how to handle NULLs that LAG() returns for the first row — a subtle edge case that separates good analysts from great ones.
4. “What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()? When would you use each one?” — This conceptual question appears at virtually every company as a warm-up. But don’t underestimate it — interviewers are listening for precision. ROW_NUMBER() gives unique sequential integers regardless of ties. RANK() skips numbers after ties. DENSE_RANK() never skips. Give a concrete business example for each: use ROW_NUMBER() for deduplication, RANK() for leaderboards where gaps matter, DENSE_RANK() for percentile bands.
5. “Write a query to find the running total of sales for each salesperson, reset at the beginning of each month.” — This tests PARTITION BY with date truncation, specifically PARTITION BY salesperson_id, DATE_TRUNC(‘month’, sale_date). It’s a common question at Flipkart Commerce Intelligence and Amazon’s analytics teams because it mirrors real reporting requirements perfectly.

The SQL Code You Need to Know Cold: Window Functions in Action

Theory only gets you so far. Interviewers want to see you write clean, correct SQL under pressure. Below is a comprehensive SQL example that covers the most critical window functions in one cohesive query — the kind you’d realistically write when analysing customer order data at a company like Swiggy or Meesho. Study this structure, understand what each clause does, and practice modifying it for different scenarios. The more fluent your hands are at the keyboard, the calmer your brain will be in the interview room.

-- Scenario: Analyse customer orders at an e-commerce platform (e.g., Flipkart/Meesho)
-- Table: orders (order_id, customer_id, city, order_date, order_value)

SELECT
    order_id,
    customer_id,
    city,
    order_date,
    order_value,

    -- Rank customers by order value within each city
    RANK() OVER (
        PARTITION BY city
        ORDER BY order_value DESC
    ) AS city_rank,

    -- Dense rank (no gaps in ranking, useful for tier segmentation)
    DENSE_RANK() OVER (
        PARTITION BY city
        ORDER BY order_value DESC
    ) AS city_dense_rank,

    -- Running total of order value per customer over time
    SUM(order_value) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,

    -- 3-day rolling average of order value per city
    AVG(order_value) OVER (
        PARTITION BY city
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3day_avg,

    -- Previous order value for the same customer (to detect repeat behaviour)
    LAG(order_value, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS prev_order_value,

    -- Next order value for the same customer (forward-looking metric)
    LEAD(order_value, 1, NULL) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS next_order_value,

    -- Assign customers to quartiles based on order value within city
    NTILE(4) OVER (
        PARTITION BY city
        ORDER BY order_value DESC
    ) AS value_quartile

FROM orders
WHERE order_date >= '2026-01-01'
ORDER BY city, order_date;

-- To filter only top-ranked orders per city, wrap in a subquery:
SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY city
            ORDER BY order_value DESC
        ) AS rn
    FROM orders
) ranked
WHERE rn = 1;