Why SQL Window Functions Are the Ultimate Data Analyst Interview Filter

Let’s be honest — most SQL basics like GROUP BY, JOIN, and WHERE are table stakes. Every candidate who applies for a data analyst role at Swiggy, Amazon, or Razorpay claims to know SQL. But window functions? That’s where the real filtering happens.

Window functions allow you to perform calculations across a set of table rows that are related to the current row — without collapsing those rows the way GROUP BY does. Think of it as performing aggregations while still keeping every individual row visible in your result set. It’s a powerful concept, and it maps directly to the kind of analytical thinking companies expect from a senior data analyst.

In the Indian tech ecosystem specifically, companies like Flipkart, Meesho, PhonePe, and Zomato deal with massive transactional datasets where ranking customers by purchase frequency, computing 7-day rolling revenue, or identifying the first order per user per city are daily tasks. These are not hypothetical problems — they’re the exact questions your interviewer wrote last Tuesday while reviewing an actual business dashboard.

According to feedback from 800+ candidates I’ve mentored, over 70% of data analyst technical rounds at product companies include at least one window function question. Yet fewer than 30% of candidates can answer it confidently and correctly. That gap is your opportunity. Understanding window functions deeply doesn’t just help you pass interviews — it makes you a genuinely better analyst who can write faster, cleaner queries on the job.

The OVER() clause is the gateway to window functions. Combined with PARTITION BY and ORDER BY, it unlocks a family of functions including ranking functions (ROW_NUMBER, RANK, DENSE_RANK), aggregate functions (SUM, AVG, COUNT), and navigation functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE). Let’s dig in.

The Most Asked SQL Window Function Interview Questions at Top Companies

Based on real interview feedback from candidates who’ve appeared at Flipkart, Amazon, Swiggy, Zomato, PhonePe, Meesho, Paytm, CRED, and Razorpay, here are the window function questions that come up again and again. These aren’t random trivia — each question maps to a real business problem these companies solve every day. Prepare answers and working SQL for each of these before your next interview round.

1. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()? Write a query to find the top 3 customers by total spend per city — handling ties correctly using each function, and explain when you’d choose one over the other.
2. Using LAG() or LEAD(), write a SQL query to calculate the month-over-month revenue growth percentage for each product category — and identify categories where revenue dropped compared to the previous month.
3. Write a query to find the first order placed by each user on the Swiggy platform (one row per user), along with the total number of orders that user has placed till date — using window functions only, without subqueries or CTEs.
4. How does PARTITION BY differ from GROUP BY? Give a real-world example where using PARTITION BY gives you something GROUP BY cannot, with reference to a dataset like a ride-hailing or e-commerce order table.
5. Write a query to compute a 7-day rolling average of daily active users (DAU) for a product like PhonePe’s UPI feature — using the ROWS BETWEEN window frame clause — and explain what happens if you change it to RANGE BETWEEN.
6. Given a table of employee salaries, write a SQL query using NTILE() to divide employees into 4 salary quartiles per department, and find which quartile has the highest average attrition rate.

SQL Code Walkthrough: Solving the Classic “Top N Per Group” Problem with Window Functions

The “Top N per group” question is arguably the single most common window function problem in data analyst interviews. It appears in some form in nearly every technical round. The scenario is always business-relevant: find the top 2 selling products per category, find the 3 most active users per city, find the highest-paid employee per department. The solution always follows the same elegant pattern using ROW_NUMBER() inside a CTE or subquery. Study this template until it’s second nature — and then practice adapting it to different table structures.

-- Scenario: Find the top 2 customers by total order value
-- per city on a platform like Flipkart or Meesho

-- Step 1: Create a base aggregation (total spend per customer per city)
-- Step 2: Apply ROW_NUMBER() partitioned by city, ordered by total_spend DESC
-- Step 3: Filter to only rows where rank <= 2

WITH customer_spend AS (
    SELECT
        customer_id,
        city,
        SUM(order_value) AS total_spend
    FROM orders
    WHERE order_status = 'delivered'
      AND order_date >= '2025-01-01'
    GROUP BY customer_id, city
),

ranked_customers AS (
    SELECT
        customer_id,
        city,
        total_spend,
        ROW_NUMBER() OVER (
            PARTITION BY city          -- Ranking resets for each city
            ORDER BY total_spend DESC  -- Highest spender gets rank 1
        ) AS spend_rank
    FROM customer_spend
)

SELECT
    city,
    customer_id,
    total_spend,
    spend_rank
FROM ranked_customers
WHERE spend_rank <= 2  -- Keep only top 2 per city
ORDER BY city, spend_rank;


-- BONUS: Month-over-Month Revenue Change using LAG()
-- Great for Swiggy/Zomato style GMV analysis

SELECT
    order_month,
    monthly_revenue,
    LAG(monthly_revenue, 1) OVER (ORDER BY order_month) AS prev_month_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY order_month))
        * 100.0
        / LAG(monthly_revenue, 1) OVER (ORDER BY order_month),
        2
    ) AS mom_growth_pct
FROM (
    SELECT
        DATE_TRUNC('month', order_date) AS order_month,
        SUM(order_value)               AS monthly_revenue
    FROM orders
    WHERE order_status = 'delivered'
    GROUP BY DATE_TRUNC('month', order_date)
) monthly_data
ORDER BY order_month;