SQL window functions have revolutionized the way data professionals analyze and extract insights from relational databases. Whether you are preparing for a data science interview or aiming to optimize your data queries, mastering window functions is essential. 

SQL Window Functions Application

What Are SQL Window Functions?

Window functions, also known as analytic functions, perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which return a single value after grouping, window functions allow you to retain the original row structure while adding computed columns.

Syntax Overview

SELECT column1, 
       window_function() OVER (
           PARTITION BY col2 
           ORDER BY col3 
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
FROM table_name;

The core components inside the OVER() clause are:

• PARTITION BY: Divides rows into partitions to apply the function independently to each partition.
• ORDER BY: Orders rows within each partition.
• Frame specification (optional): Defines the window frame for the calculation.

Types of SQL Window Functions

Window functions can be categorized into several types, each with distinct use cases:

• Aggregate Window Functions: SUM(), AVG(), MIN(), MAX(), COUNT()
• Ranking Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
• Value Window Functions: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()

1. Aggregate Window Functions in SQL

Aggregate functions as window functions calculate cumulative or rolling values over a dynamically defined "window" of rows.

Example 1: Cumulative Salary of Employees

Suppose you have an employees table:

To calculate the cumulative salary of employees in each department, ordered by salary:

SELECT emp_id,
       emp_name,
       salary,
       department,
       SUM(salary) OVER (
           PARTITION BY department 
           ORDER BY salary 
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_salary
FROM employees;

This query returns the running total of salary within each department.

Example 2: Moving Average of Salary

To compute the moving average of salary for each employee:

SELECT emp_id, emp_name, salary,
       AVG(salary) OVER (
           ORDER BY emp_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_salary
FROM employees;

This calculates the average salary over the current and two previous employees, ordered by emp_id.

2. Ranking Window Functions

Ranking functions assign ranks or numbers to rows based on specified criteria—crucial for common interview questions like "Nth highest salary" or "Top 3 earners in each department."

ROW_NUMBER()

Assigns a unique sequential integer to rows within a partition.

SELECT emp_id, emp_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

RANK()

Gives the same rank to ties and leaves a gap after the tie.

SELECT emp_id, emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS emp_rank
FROM employees;

DENSE_RANK()

Like RANK(), but without gaps after ties.

SELECT emp_id, emp_name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS emp_dense_rank
FROM employees;

NTILE(n)

Divides the result set into n buckets of approximately equal size.

SELECT emp_id, emp_name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

3. Value Window Functions

Value functions allow you to access data from preceding or following rows relative to the current row.

LAG() and LEAD()

• LAG(): Accesses data from a previous row.
• LEAD(): Accesses data from a subsequent row.

SELECT emp_id, emp_name, salary,
       LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;

FIRST_VALUE() and LAST_VALUE()

SELECT emp_id, emp_name, salary,
       FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS highest_salary,
       LAST_VALUE(salary) OVER (ORDER BY salary DESC 
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM employees;

Common SQL Window Function Interview Problems

1. Nth Highest Salary

Find the Nth highest salary from the employees table.

WITH RankedSalaries AS (
    SELECT salary, 
           DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT salary
FROM RankedSalaries
WHERE salary_rank = 3; -- For 3rd highest salary

2. Employee Score Rank

Suppose you have a scores table:

SELECT emp_id, score,
       RANK() OVER (ORDER BY score DESC) AS score_rank
FROM scores;

3. Top 3 Earners in Each Department

SELECT emp_id, emp_name, salary, department
FROM (
    SELECT emp_id, emp_name, salary, department,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
) ranked
WHERE dept_rank <= 3;

This retrieves the top three earners per department.

4. Finding Median From Frequency Table

Suppose you have a table frequency:

The median is the value at the position \( \left\lceil \frac{N}{2} \right\rceil \) in the ordered data, where \( N \) is the total count.

WITH Expanded AS (
    SELECT number, frequency,
           SUM(frequency) OVER (ORDER BY number) AS cumulative_freq
    FROM frequency
), Totals AS (
    SELECT SUM(frequency) AS total_count FROM frequency
)
SELECT number
FROM Expanded, Totals
WHERE cumulative_freq >= (total_count + 1) / 2
ORDER BY number
LIMIT 1;

This finds the median from a frequency distribution.

5. Cumulative Product or Sum Per Group

Suppose you want to calculate the cumulative sum of sales per region:

SELECT region, sales,
       SUM(sales) OVER (PARTITION BY region ORDER BY date) AS region_cum_sales
FROM sales_data;

Advanced SQL Window Function Applications

1. Detecting Gaps in Time Series Data

Given a logins table:

To find missing dates in login sequence:

SELECT user_id, login_date,
       LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login,
       DATEDIFF(login_date, LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date)) AS days_gap
FROM logins;

Rows with days_gap > 1 indicate missing dates.

2. Calculating Running Percentage

To compute each employee's cumulative salary as a percentage of the total department salary:

SELECT emp_id, emp_name, salary, department,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS department_cum_salary,
       SUM(salary) OVER (PARTITION BY department) AS department_total_salary,
       100.0 * SUM(salary) OVER (PARTITION BY department ORDER BY salary) /
              SUM(salary) OVER (PARTITION BY department) AS running_percent
FROM employees;

3. Group-wise Top-N with Ties

To get all employees whose salary is among the top 3 (including ties) in their department:

SELECT emp_id, emp_name, salary, department
FROM (
    SELECT emp_id, emp_name, salary, department,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
) ranked
WHERE dept_rank <= 3;

4. Calculating Percentile Rank

Percentile rank can be calculated using the PERCENT_RANK() function.

SELECT emp_id, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS percentile_rank
FROM employees;

5. Running Median (Sliding Window)

For each row, find the median salary over a moving window of 3 employees:

SELECT emp_id, salary,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
       OVER (ORDER BY emp_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS running_median
FROM employees;

Note: PERCENTILE_CONT() is available in PostgreSQL, Oracle, and some other databases.

Window Frame Clauses Explained

The frame clause defines the subset of rows used for each calculation. The default is:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• UNBOUNDED PRECEDING: The first row of the partition.
• CURRENT ROW: The current row.
• 1 PRECEDING: One row before the current row.
• UNBOUNDED FOLLOWING: The last row of the partition.

Examples:

-- Running total
SUM(salary) OVER (ORDER BY emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Moving 3-row average
AVG(salary) OVER (ORDER BY emp_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Performance Considerations

Window functions can be computationally expensive, especially with large datasets. Always:

• Index columns used in PARTITION BY and ORDER BY
• Avoid unnecessary window frames
• Use LIMIT or filter partitions where possible

Test your queries

thoroughly, especially when working with millions of rows. The execution plan can reveal bottlenecks, such as expensive sorts or repeated scans of the same data. Optimizing queries with proper indexing and minimizing the number of windowed calculations in a single query are vital for scaling analytics workloads.

Window Functions vs. GROUP BY: When to Use Which?

A common question in interviews is: When should you use window functions instead of GROUP BY?

• GROUP BY collapses rows into a single output row per group — you lose row-level detail.
• Window Functions retain the original row structure, adding extra computed columns — you keep row-level detail while gaining aggregated insights.

Example: Suppose you want to display each employee’s salary along with the average department salary.

-- Using GROUP BY (loses detail)
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Using Window Function (keeps detail)
SELECT emp_id, emp_name, salary, department,
       AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

Window Functions in Real-World Data Science Scenarios

Window functions are not just for interview puzzles—they’re core to real-world analytics and business intelligence. Here are some practical applications:

• Customer Analytics: Cohort analysis, customer ranking, and lifecycle metrics.
• Finance: Calculating moving averages, running totals, or time-weighted returns.
• Product Analytics: Feature usage streaks, churn detection, or conversion funnels.
• Operations: Detecting process gaps, bottlenecks, or anomalies in time-series logs.

Example: Cohort Retention Analysis

Suppose you want to track user retention by signup month:

SELECT user_id,
       DATE_TRUNC('month', signup_date) AS cohort_month,
       login_date,
       DATEDIFF(day, signup_date, login_date) AS days_since_signup,
       COUNT(*) OVER (
           PARTITION BY DATE_TRUNC('month', signup_date), 
                        DATEDIFF(day, signup_date, login_date)
       ) AS retained_users
FROM user_logins;

This lets you build a retention curve for each signup cohort.

Advanced Interview-Ready Window Function Problems

1. Find Employees Who Earn More Than the Department Average

SELECT emp_id, emp_name, salary, department
FROM (
    SELECT emp_id, emp_name, salary, department,
           AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
    FROM employees
) sub
WHERE salary > avg_dept_salary;

2. Calculate Cumulative Distribution (CUME_DIST)

Cumulative distribution shows the proportion of rows with a value less than or equal to the current row.

SELECT emp_id, salary,
       CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;

3. Find Consecutive Days of Activity (Streaks)

Given a user_activity table with user_id and activity_date, find streaks of consecutive days:

SELECT user_id, activity_date,
       DATEDIFF(day, '2000-01-01', activity_date) -
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS streak_group
FROM user_activity;

Rows with the same streak_group value belong to the same streak.

4. Calculate Year-over-Year Growth by Partition

Suppose you have a sales table with year, region, and sales_amount:

SELECT year, region, sales_amount,
       sales_amount - LAG(sales_amount) OVER (PARTITION BY region ORDER BY year) AS yoy_growth
FROM sales;

5. Find the First Time a Value Crosses a Threshold

Given transactions with user_id, tx_date, and amount, find the first date each user's cumulative amount exceeds $1000:

WITH Cumulative AS (
    SELECT user_id, tx_date, amount,
           SUM(amount) OVER (PARTITION BY user_id ORDER BY tx_date) AS cum_amount
    FROM transactions
)
SELECT user_id, MIN(tx_date) AS first_cross_date
FROM Cumulative
WHERE cum_amount >= 1000
GROUP BY user_id;

Window Functions and Mathjax: Understanding the Math

Many window function problems are rooted in mathematical logic. For example, the median in a sequence of numbers of size \( N \) is the value at position:

\[ \text{Median Position} = \left\lceil \frac{N}{2} \right\rceil \]

For percentile calculations using PERCENT_RANK():

\[ \text{Percentile Rank} = \frac{\text{Number of values less than current}}{N-1} \]

Understanding these equations helps you construct proper window queries for advanced analytics.

Tips for Mastering SQL Window Functions

• Practice: Use platforms like LeetCode, Hackerrank, or SQLZoo for window function problems.
• Visualize: Draw out the partitions and orderings on paper to understand how rows are grouped and numbered.
• Read Execution Plans: Understand how your database executes window queries for optimization.
• Know Database Differences: Syntax and function support can vary between PostgreSQL, MySQL, SQL Server, and Oracle.
• Combine Functions: Nest window functions with CTEs for complex logic, such as ranking within moving windows or filtering on windowed results.

Conclusion

SQL window functions are indispensable for anyone working in data science, analytics, or engineering. They offer unmatched flexibility for complex calculations, rankings, and aggregations—without sacrificing row-level detail. Whether you’re preparing for technical interviews or building production dashboards, understanding how and when to use window functions will set you apart as a data professional.

Keep practicing with real-world datasets and interview-style questions. Refer back to this guide when you encounter new analytical challenges, and you’ll be well on your way to SQL mastery!

Further Reading

• SQL Window Functions Tutorial
• Mode Analytics Window Functions Guide
• Microsoft SQL Server: OVER Clause
• PostgreSQL: Window Functions

Related Articles

• Jane Street Quantitative Trader Interview Question: Probability of Overlapping Random Intervals
• Jane Street Quantitative Trader Interview Question: Optimal Stopping Problem with Marbles
• WorldQuant Quant Researcher Interview Question: Pattern Recognition Encoding Puzzle (Color Codes)
• Time And Space Complexity of Algorithms
• Inventory Risk in Trading: How Market Makers Manage Exposure