Window Functions Interview Questions

46 real Window Functions questions asked in live technical interviews — each with a model answer. Updated weekly.

🎤 Auto-captured by Assisting AI during live interviews

These Window Functions interview questions were captured from real interviews by candidates using Assisting AI. Each links to a full model answer. For real-time help during your own interview, get Assisting AI from ₹500/day.

Write a simple SQL query to find customers who placed orders on three consecutive days.🟡 Medium · Coding · Asked 2×Given a table with daily amounts, write a query or script to calculate the increase in amount compared to the previous day's record and add a new column called 'Increase'.🟡 Medium · Coding · Asked 1×In SQL, suppose you have a table with customer ID, transaction date, and amount. How would you find the latest transaction for each customer? Explain your approach.🟡 Medium · Coding · Asked 1×How would you find the employee(s) with the highest salary in each department using SQL?🟡 Medium · Conceptual · Asked 1×How can you retrieve the employee(s) with the highest salary in each department using a window function in SQL?🟡 Medium · Conceptual · Asked 1×Walk me through a query that uses dense_rank, rank, and row_number on an employee table with 526 employee IDs. Provide examples and explain the results.🟡 Medium · Conceptual · Asked 1×How do the LEAD and LAG functions work in SQL?🟢 Easy · Conceptual · Asked 1×Table matches(player_id, match_date, result) where result is 'W' or 'L'. Find each player's longest winning streak. Write the query.🔴 Hard · Coding · Asked 1×Explain the subtraction of row numbers in SQL.🟡 Medium · Conceptual · Asked 1×Explain the following PostgreSQL query that finds the longest streak per player.🟡 Medium · Conceptual · Asked 1×Write a fresh query to fetch the second highest salary from the employees table.🟡 Medium · Coding · Asked 1×How would you write a SQL query to find the first and last purchase dates for each customer-product pair, ensuring that each pair has at least two completed orders?🟡 Medium · Coding · Asked 1×Describe how you would use a CTE and window functions to rank purchases by date and then filter for the first and last occurrences for each customer-product pair.🟡 Medium · Coding · Asked 1×In a transaction table, how would you keep only the latest record for each entity?🟡 Medium · Conceptual · Asked 1×What are window functions in SQL?🟡 Medium · Conceptual · Asked 1×How would you use the LAG, WHEN, and CUMSUM functions to identify user session boundaries based on a 30‑minute inactivity gap?🟡 Medium · Coding · Asked 1×How does the RANK() window function work when partitioned by region_name to rank customers by total revenue, and how would you write such a query?🟡 Medium · Conceptual · Asked 1×Use the RANK() window function partitioned by region to retrieve the top 3 customers per region by revenue.🟡 Medium · Coding · Asked 1×Use the RANK() window function over a filtered and aggregated dataset to handle ties and retrieve the top 3 customers per region by revenue.🟡 Medium · Coding · Asked 1×Write a SQL query using a window function to retrieve the top 3 customers per region by revenue, handling ties with RANK().🟡 Medium · Coding · Asked 1×How would you use a Spark window function with row_number() partitioned by customer and ordered by timestamp descending to get the latest order per customer?🟡 Medium · Coding · Asked 1×What is the output of the ROW_NUMBER() function in SQL?🟢 Easy · Conceptual · Asked 1×How do you calculate a running total, such as a running salary, where the first entry is 1000 rupees and the second is 3000 rupees, accumulating over time?🟡 Medium · Coding · Asked 1×For each employee, generate a new column that contains the running total of salaries.🟡 Medium · Coding · Asked 1×Write an SQL query that, given a table with columns id, year, and revenue, returns for each row the previous year's revenue and the revenue difference.🟡 Medium · Coding · Asked 1×Using a window function, how would you find duplicate rows in a table using SQL?🟡 Medium · Coding · Asked 1×Write an SQL query that returns the top two highest‑paid employees from each department.🟡 Medium · Coding · Asked 1×Write an SQL query that joins the Employee and Department tables to display employee name, department name, and salary, and then uses a window function to rank employees by salary within each department.🟡 Medium · Coding · Asked 1×Find the top three products in each category.🟡 Medium · Coding · Asked 1×Row numbers are used, but they don't check for consecutive purchases. The number only indicates whether there are more than three purchases. How can we modify this to check for consecutive purchases?🟡 Medium · Coding · Asked 1×Customer ID 1 meets the criteria because they made purchases on 25th Jan, 26th Jan, and 27th Jan – three consecutive days. Customer ID 2 does not meet the criteria because, although they made more than three purchases, the dates are not consecutive. Write an SQL query to find customer IDs of customers who have made purchases on at least three consecutive days.🟡 Medium · Coding · Asked 1×What is the difference between LAG and LEAD functions?🟢 Easy · Conceptual · Asked 1×Are you familiar with window functions? Can you explain any window function you have used in real time?🟡 Medium · Conceptual · Asked 1×Can you get a single dense rank?🟢 Easy · Conceptual · Asked 1×Can you explain rank, dense rank, and row number?🟡 Medium · Conceptual · Asked 1×Can you tell the difference between rank, dense rank, and row number?🟡 Medium · Conceptual · Asked 1×Can you explain the difference between the DENSE_RANK and ROW_NUMBER window functions?🟡 Medium · Conceptual · Asked 1×Are you familiar with SQL window functions?🟢 Easy · Conceptual · Asked 1×Find the top 3 highest salaries in each department.🟡 Medium · Coding · Asked 1×How would you find the second highest paid employee in each department?🟡 Medium · Coding · Asked 1×Briefly explain row number, rank, and dense rank.🟡 Medium · Conceptual · Asked 1×Provide code for dense rank and row number.🟡 Medium · Coding · Asked 1×Explain the differences between ROW_NUMBER, RANK, and DENSE_RANK window functions.🟡 Medium · Conceptual · Asked 1×What is the difference between window functions and aggregate functions?🟡 Medium · Conceptual · Asked 1×You need the latest record per (id) from a change‑log table with multiple updates per day and occasional ties on timestamp. Which SQL pattern is most reliable?🟡 Medium · Conceptual · Asked 1×How can you compute a cumulative sum (running balance) ordered by date using an unbounded preceding window specification in a Spark DataFrame?🟡 Medium · Conceptual · Asked 1×

🎤 Get Window Functions questions answered in real-time

Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.

Get Assisting AI — Starts at ₹500

Browse Other Topics