SQL Window Functions Interview Questions
Window functions are the most common topic in mid-level and senior SQL interviews. Rank rows within groups, compare each row with the previous one and compute running totals without collapsing your result.
20 questions · 13 Medium · 7 Hard · about 4.4 hours · MySQL & PostgreSQL
What you'll practise
- ROW_NUMBER, RANK and DENSE_RANK
- PARTITION BY
- LAG and LEAD
- Running totals
- Moving averages and window frames
Learn the concepts first
Questions in this plan
Rank Salaries Within Department
Assign dense rank values to employee compensations per department without skipping ranks for tied salaries.
Month-over-Month Revenue Growth
Compute monthly successful payment volume and its percentage change versus the prior month.
Running Total of Daily Sales
Calculate a cumulative running balance of transaction amounts partitioned by retail store location.
Amazon: First Order Details
Retrieve each customer's very first order, resolving date ties by selecting the lowest order_id.
Amazon: Days Between Repeat Orders
Calculate the average gap in days between consecutive purchases for customers with repeat orders.
Amazon: Best-Selling Products per Category
Identify the top-selling product in each retail category by total units sold, returning all tied leaders using DENSE_RANK.
Amazon: Customer Spend Quartiles
Divide purchasing customers into 4 spend quartiles using NTILE based on lifetime transaction total.
Most Watched Title per Genre
Find the title with the highest total watch time in each genre, keeping ties.
3-Day Moving Average of Orders
Smooth daily order counts with a trailing three-day average.
Accounts That Went Negative
Use running balances to find accounts that ever overdrew and when it first happened.
Current and Previous Subscription Plan
From a plan-change history, return each user's current plan and the one before it.
Average Days Between Orders
Measure each repeat customer's average gap between consecutive orders.
Items Priced Above Category Average
List products whose price is higher than the average price of their own category.
User Consecutive 3-Day Login Streak
Detect users who logged into Google services on at least three consecutive calendar days.
Google Ads: Top 2 Performing Ads per Category
Use DENSE_RANK() window function to rank and filter the top 2 highest click-through rate ads within each advertising category.
Department Top Three Salaries
Use DENSE_RANK() window function to find high earners in each separate department.
Amazon: Monthly Cohort Retention
Analyze customer retention cohorts across 1-month and 2-month subsequent repurchase cycles.
Longest Login Streaks
Find users who logged in on at least three consecutive days and report their best streak.
User Session Reconstruction
Group raw click events into sessions using a 30-minute inactivity rule.
Median Order Value by Region
Compute the true median order value per region without a built-in median function.