Analytic Functions in SQL: Complete Guide with Examples

SQL analytic functions (or window functions) have become one of the most frequently tested topics in data analyst and data engineer interviews—and a must-have skill for real-world analytics and BI dashboards. Whether you’re analyzing revenue trends, ranking customers, or calculating rolling averages, these functions let you perform complex calculations without losing the individual row detail that’s often crucial for business insights.

As data teams increasingly rely on SQL-driven reporting, mastering analytic functions is what separates routine query writers from true data storytellers. This makes them indispensable for data analysts, engineers, and scientists working with real-world datasets where context matters just as much as the numbers.

In this guide, we’ll walk through everything you need to know, from syntax and examples to database differences, optimization tips, and interview-style questions, so you can confidently use analytic functions to uncover deeper insights directly within SQL.

SQL analytic functions—often called window functions, are a powerful toolkit that lets you perform calculations across a set of rows while still keeping the individual row detail. Unlike traditional aggregation functions (such as SUM() or AVG() with GROUP BY), which collapse multiple rows into a single summary result, analytic functions allow you to compute metrics like running totals, rankings, or moving averages without losing the granularity of your dataset.

Read more: SQL Aggregate Functions Explained: Examples and Best Practices

Aggregation Functions vs Analytic Functions

Aspect	Aggregation Functions	Analytic (Window) Functions
Purpose	Summarize data into a single result per group	Calculate metrics across a defined “window” of rows while retaining individual rows
Result Output	Returns one row per group	Returns one row per input row
Key Clause Used	GROUP BY	OVER() with optional PARTITION BY and ORDER BY
Example Use Case	Total revenue by region → SUM(revenue) GROUP BY region	Cumulative revenue over time per product → SUM(revenue) OVER(PARTITION BY product ORDER BY date)
Common Examples	SUM(), AVG(), COUNT()	RANK(), ROW_NUMBER(), LAG(), LEAD(), SUM() OVER()
Use in Analytics	Produces grouped summaries for reports	Enables advanced analytics like ranking, moving averages, and running totals directly in SQL
Output Behavior	Reduces rows	Preserves all rows with additional computed columns

This distinction makes analytic functions especially important for analytics and reporting. They enable analysts to answer questions such as: What is the cumulative revenue over time for each product?, How does a customer rank by total purchases within their region? Or What’s the difference between a value and the average for its category?

By providing these insights directly within SQL, analytic functions reduce the need for extra post-processing in spreadsheets or BI tools and support cleaner, more efficient query logic.

When working with analytic (window) functions in SQL, the syntax revolves around the OVER() clause. This clause defines the “window” of rows over which the function operates, letting you control whether calculations are performed across the entire dataset, per group, or within a specific ordering of rows. By combining OVER() with options like PARTITION BY, ORDER BY, and frame definitions (ROWS BETWEEN or RANGE BETWEEN), you can build powerful and precise analytic queries.

OVER() Clause Explained

The OVER() clause is the backbone of analytic functions. It tells SQL to compute the function for each row while preserving row-level detail.

function_name(column) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [ROWS | RANGE frame_specification]
)

• Without arguments, OVER() applies the function across all rows.
• With PARTITION BY, it resets calculations for each group.
• With ORDER BY, it adds row sequencing, enabling cumulative or ranking functions.

PARTITION BY Clause with Example

PARTITION BY divides the dataset into groups before applying the window function. Think of it as similar to GROUP BY, but instead of collapsing rows, it keeps them visible.

Example: calculate total sales per region while keeping row detail:

SELECT 
    region,
    customer_id,
    SUM(sales) OVER (PARTITION BY region) AS regional_sales
FROM orders;

Here, each row shows a customer’s sales along with the total sales of their region.

ORDER BY for Window Functions

Adding an ORDER BY inside OVER() introduces sequencing. This is crucial for cumulative metrics, running totals, or ranking.

Example: cumulative sales per customer:

SELECT 
    customer_id,
    order_date,
    SUM(sales) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS running_total
FROM orders;

Now, each row shows how sales accumulate over time for each customer.

ROWS BETWEEN vs RANGE BETWEEN (with diagrams)

Window frames specify which rows are included in the calculation relative to the current row.

• ROWS BETWEEN → Counts rows explicitly (physical positioning).
• RANGE BETWEEN → Considers values of the ORDER BY expression (logical range).

Example: Moving average of last 3 orders vs last 30 days.

-- ROWS: last 3 rows including current
AVG(sales) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

-- RANGE: all rows within 30 days before current row
AVG(sales) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW
)

SQL analytic functions can be grouped into several categories, each serving a different analytical purpose. By understanding these types, you can choose the right function for reporting, trend analysis, and performance tracking directly in SQL.

Ranking Functions

Ranking functions assign positions or “ranks” to rows within a partition, usually based on an ORDER BY clause. They’re useful for leaderboards, percentile calculations, or identifying top-N items.

• ROW_NUMBER(): assigns a unique, sequential number to each row
• RANK(): assigns ranks but skips numbers if there are ties
• DENSE_RANK(): like RANK(), but doesn’t skip ranks when ties occur
• NTILE(n): divides rows into n buckets, often used for percentiles or quartiles

SELECT 
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Here, employees with the same salary get the same rank, but RANK() will skip numbers while DENSE_RANK() will not.

Window Aggregate Functions

These apply traditional aggregation functions across a defined window without collapsing rows. They’re ideal for cumulative metrics, moving averages, or comparisons within groups.

• SUM() OVER(): cumulative totals
• AVG() OVER(): moving or group averages
• MIN(), MAX(), COUNT(): provide row-level context like min/max per group or rolling counts

SELECT 
    region,
    customer_id,
    SUM(sales) OVER (PARTITION BY region) AS total_region_sales,
    AVG(sales) OVER (PARTITION BY region) AS avg_region_sales
FROM orders;

Each row shows customer sales alongside the total and average sales for their region.

Statistical Window Functions

Statistical functions give deeper insight into data variability and distribution within a window.

• STDDEV(): calculates the standard deviation
• VARIANCE(): measures the variance within a window

SELECT 
    department_id,
    salary,
    STDDEV(salary) OVER (PARTITION BY department_id) AS dept_stddev,
    VARIANCE(salary) OVER (PARTITION BY department_id) AS dept_variance
FROM employees;

This highlights how salaries vary within each department, helping identify outliers or pay gaps.

Offset Functions

Offset functions compare current row values with those of preceding or following rows. These are extremely valuable for trend analysis and sequential comparisons.

• LAG(): returns a value from a previous row
• LEAD(): returns a value from the following row
• FIRST_VALUE(): returns the first value in the window
• LAST_VALUE(): returns the last value in the window

SELECT 
    order_date,
    sales,
    LAG(sales, 1) OVER (ORDER BY order_date) AS prev_sales,
    LEAD(sales, 1) OVER (ORDER BY order_date) AS next_sales
FROM orders;

Here, you can track how today’s sales compare to yesterday’s or tomorrow’s.

By combining these categories, you can build advanced analytical queries—ranking top performers, measuring variability, tracking trends, and calculating rolling metrics—all without leaving SQL.

While analytic (window) functions follow the SQL standard, each database has its own quirks in syntax, available functions, and performance optimizations. Understanding these differences is crucial when working across platforms or migrating queries between systems.

Syntax Differences + Examples

The core syntax for analytic (window) functions remains consistent across most modern SQL databases, following the ANSI SQL standard:

function_name(column) OVER (
    [PARTITION BY expression]
    [ORDER BY expression]
    [ROWS | RANGE frame_specification]
)

• PostgreSQL and SQL Server adhere closely to the SQL standard, making their syntax straightforward.
• Oracle provides the most comprehensive set of analytic functions with flexible window specifications.
• MySQL (8.0+) introduced window functions more recently, covering ranking and aggregates, but with simpler frame options.
• Snowflake fully supports the ANSI standard, offering rich analytic capabilities optimized for large-scale, distributed workloads.
• BigQuery also supports analytic functions extensively, especially for time-series, ranking, and partitioned datasets, though with some unique extensions (e.g., QUALIFY for filtering windowed results).

Example (ROW_NUMBER across systems):

-- Works across PostgreSQL, SQL Server, Oracle, MySQL 8+, Snowflake, and BigQuery
SELECT 
    customer_id, region,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) AS row_num
FROM orders;

This works nearly identically across all four databases, but frame handling (ROWS BETWEEN vs RANGE BETWEEN) can vary slightly.

Notes:

• In BigQuery, you can filter on window function results directly using QUALIFY, e.g.:

SELECT
customer_id,
region,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) AS row_num
FROM orders
QUALIFY row_num <= 3;

This eliminates the need for an outer query.

• In Snowflake, the syntax is identical to the ANSI SQL version and supports advanced time-based windowing (e.g., ROWS BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW).

In short, while all six databases follow the same foundational syntax, BigQuery and Snowflake extend it for large-scale analytics, while Oracle remains the most feature-rich for complex enterprise logic.

Oracle-Specific Analytic Functions (LISTAGG, PIVOT)

Oracle has been a pioneer in analytic functions and offers specialized options:

• LISTAGG(): concatenates values within a group into a single string.

SELECT department_id,
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM employees
GROUP BY department_id;

• PIVOT (and UNPIVOT): allows reshaping of data directly in SQL, often used for cross-tab reporting and analytics.

    SELECT *
    FROM (
      SELECT department, quarter, revenue
      FROM sales_data
    )
    PIVOT (
      SUM(revenue)
      FOR quarter IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4)
    )
    ORDER BY department;

What it does:

This query transforms quarterly revenue data into a tabular cross-report, showing each department as a row and quarters (Q1–Q4) as columns, with summed revenues as values.

Oracle also supports advanced features like model clauses for spreadsheet-like calculations, making it especially strong for enterprise reporting.

Comparing Analytic Function Support Across Major Databases (SQL Server, Oracle, PostgreSQL, MySQL, Snowflake & BigQuery)

Different SQL databases vary in how deeply they support analytic and window functions. Here’s a quick comparison of their strengths, unique features, and limitations across the databases.

Database	Analytic Function Support	Unique Strengths / Features	Limitations / Notes
SQL Server	Robust set of ranking, aggregate, and offset functions	- Full support for ROWS BETWEEN frame clauses - Includes NTILE() and other reporting-friendly extensions	Mature implementation but slightly less flexible than Oracle for advanced statistical use cases
Oracle	Most comprehensive analytic function library	- Advanced options like LISTAGG, PIVOT, RATIO_TO_REPORT, and statistical measures - Highly flexible window specifications	Enterprise-focused; can be complex for beginners
PostgreSQL	Fully ANSI SQL-compliant with broad analytic coverage	- Strong support for custom functions and extensions - Excellent for analytics and BI integrations	Slightly slower than Oracle/SQL Server in very large-scale workloads
MySQL (8.0+)	Recently added window function support	- Supports ranking, aggregate, and offset functions - Simple and easy-to-learn frame definitions	Lacks some advanced Oracle-style functions and statistical extensions
Snowflake	Full modern window function support with SQL standard syntax	- Optimized for large-scale analytical workloads - Native support for cumulative metrics, ranking, and time-based windowing - Integrates smoothly with BI and ETL tools	Slightly limited in advanced statistical functions compared to Oracle; pricing tied to compute time
BigQuery	Extensive support for window and analytic functions	- Designed for distributed processing; performs analytic functions efficiently on billions of rows - Strong support for time-series, partitioned queries, and complex analytics	Some non-standard quirks in syntax; limited procedural extensions compared to SQL Server/Oracle

In short: Enterprise databases like Oracle and SQL Server dominate traditional analytics, while Snowflake and BigQuery lead in cloud-native analytic performance and scalability. PostgreSQL remains the open-source standard, and MySQL provides lightweight coverage for smaller workloads.

Analytic functions shine when applied to real-world business and reporting problems. By keeping the row-level detail intact while adding powerful calculations, they reduce the need for external tools and streamline insights directly in SQL. Here are a few examples across industries:

Read more: SQL Scenario Based Interview Questions (With Answers & Real Examples)

Running Totals in Finance

In banking or accounting, companies often need to track cumulative balances, loan repayments, or investment growth. Analytic functions like SUM() OVER(ORDER BY …) make this easy without collapsing transactions.

SELECT 
    account_id,
    transaction_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id 
        ORDER BY transaction_date
    ) AS running_balance
FROM transactions;

Each transaction row shows both the individual amount and the account’s updated balance over time.

Ranking Customers in E-commerce

E-commerce companies use ranking functions to identify top customers by spending, order frequency, or recency. This drives loyalty programs, targeted promotions, and churn prevention.

SELECT 
    customer_id,
    SUM(order_value) AS total_spent,
    RANK() OVER (ORDER BY SUM(order_value) DESC) AS spend_rank
FROM orders
GROUP BY customer_id;

Analysts can quickly see the highest-value customers, enabling VIP segmentation.

Rolling Averages for KPIs

Businesses track KPIs like sales, website traffic, or call center volume. Instead of raw daily numbers, rolling averages smooth out fluctuations and reveal true performance trends.

SELECT 
    order_date,
    AVG(sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM daily_sales;

This gives a 7-day moving average, useful for management dashboards.

Flight Duration Analysis

In aviation, airlines can analyze flight times by route to identify delays or operational inefficiencies. Offset functions like LAG() or LEAD() help compare flights sequentially.

SELECT 
    flight_id,
    departure_airport,
    arrival_airport,
    arrival_time - departure_time AS flight_duration,
    LAG(arrival_time - departure_time) 
        OVER (PARTITION BY route ORDER BY departure_time) AS prev_duration
FROM flights;

Each row shows the current flight’s duration alongside the previous flight on the same route, making it easy to spot anomalies.

Analytic (window) functions are extremely powerful for reporting and trend analysis — but they also bring subtle pitfalls that can easily lead to wrong results or poor performance.

Below are some of the most common issues and how to fix them with practical examples.

ROWS vs RANGE Confusion

The most common mistake is misunderstanding ROWS BETWEEN vs RANGE BETWEEN in the OVER() clause.

• ROWS → counts physical rows relative to the current row.
• RANGE → counts values of the ordering column, grouping all rows that share the same value.

Example:

-- Using RANGE (groups same date values together)
SELECT
    order_date,
    sales,
    SUM(sales) OVER (
        ORDER BY order_date
        RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS range_sum
FROM orders;

If two rows share the same order_date, they’ll both be included—leading to a larger total than expected.

Safer alternative using ROWS:

-- Using ROWS (counts one physical row back)
SELECT
    order_date,
    sales,
    SUM(sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS row_sum
FROM orders;

Use ROWS when you need precise rolling calculations. Use RANGE when you truly want to group by value.

LAST_VALUE() Requires UNBOUNDED FOLLOWING

By default, LAST_VALUE() looks only up to the current row, not the true last value of the partition which is a common trap that returns misleading results.

Incorrect:

SELECT
    order_id,
    order_date,
    LAST_VALUE(sales) OVER (ORDER BY order_date) AS wrong_last
FROM orders;

This will show the current row’s value, not the last one.

Corrected version:

SELECT
    order_id,
    order_date,
    LAST_VALUE(sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS true_last
FROM orders;

Always expand your frame to include all rows (UNBOUNDED FOLLOWING) when you actually want the true last value.

Performance Issues with Large Partitions

When analytic functions scan millions of rows per partition, performance tanks due to memory-heavy operations.

Problematic query:

SELECT
    customer_id,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS total_sales
FROM transactions;

If a single customer has hundreds of thousands of records, this can choke.

Optimized approach using CTEs or staging:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM transactions
    GROUP BY customer_id
)
SELECT
    t.customer_id,
    t.order_date,
    t.amount,
    c.total_sales
FROM transactions t
JOIN customer_totals c
    ON t.customer_id = c.customer_id;

Pre-aggregate large partitions using CTEs or temp tables before applying window functions.

Indexing and ORDER BY Optimization

Analytic functions rely heavily on ORDER BY (and sometimes PARTITION BY). Without proper indexing, even simple ranking queries can drag.

Example (slow without index):

SELECT
    department_id,
    employee_name,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

To optimize:

CREATE INDEX idx_emp_dept_salary
    ON employees (department_id, salary DESC);

• Composite indexes (on both PARTITION BY and ORDER BY columns) speed up window functions dramatically.
• Limit the number of distinct ORDER BY clauses used in one query.
• Use clustered or sorted tables if you frequently query by the same partitions.

With smart indexing and frame design, you can keep analytic queries efficient even on very large datasets.

Quick Glance

Pitfall	Fix / Best Practice
Confusing ROWS vs RANGE	Use ROWS for exact rolling rows; RANGE for grouped values
LAST_VALUE() returns current row	Add UNBOUNDED FOLLOWING to include all rows
Large partitions slow queries	Pre-aggregate with CTEs or temp tables
Poor indexing	Create composite indexes on partition + order columns

In data analyst and data engineering interviews, analytic functions often appear in SQL challenges because they test both technical skill and problem-solving. Below are a few examples of common interview-style questions that showcase how window functions can be applied.

Practice more: Top 100+ Data Analyst Interview Questions

Third Purchase per User

Question: Write a query to find the third purchase (by date) for each user.

SELECT user_id, order_id, order_date
FROM (
    SELECT 
        user_id,
        order_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY order_date
        ) AS rn
    FROM orders
) t
WHERE rn = 3;

This uses ROW_NUMBER() to order purchases and filter for the third one.

Second-Longest Flight

Question: Find the second-longest flight in the dataset.

SELECT flight_id, flight_duration
FROM (
    SELECT 
        flight_id,
        (arrival_time - departure_time) AS flight_duration,
        RANK() OVER (ORDER BY (arrival_time - departure_time) DESC) AS rnk
    FROM flights
) t
WHERE rnk = 2;

Here RANK() helps handle ties—multiple flights may share the longest duration.

Last Transaction of the Day

Question: For each user, return the last transaction of every day.

SELECT user_id, transaction_date, transaction_id, amount
FROM (
    SELECT 
        user_id,
        transaction_date,
        transaction_id,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, transaction_date
            ORDER BY transaction_time DESC
        ) AS rn
    FROM transactions
) t
WHERE rn = 1;

Using ROW_NUMBER() with descending order ensures we pick the final transaction per day.

Analytic functions are one of the most commonly tested SQL concepts in both interviews and real-world analytics work. Below are answers to frequently asked questions that clarify how they differ from other SQL features and when to use them effectively.

How do Analytic Functions differ from Aggregate Functions?

Aggregate functions (e.g., SUM(), AVG(), COUNT()) operate on a group of rows and return a single summary value for that group. Analytic functions, while also performing calculations over a group of rows (a “window”), return a result for each row within that window, maintaining the original number of rows in the result set.

Do all databases support analytic functions?

Not all databases fully support analytic (window) functions. Oracle, SQL Server, and PostgreSQL offer complete support with advanced options like frame clauses, while MySQL added them starting in version 8.0 and SQLite since 3.25.0. Older or lightweight databases may lack full compatibility, so always verify your version before using analytic functions.

When should I use PARTITION BY vs GROUP BY?

• GROUP BY collapses rows into a single summary row per group. Use it when you only need aggregated results.
• PARTITION BY is part of the OVER() clause and keeps all rows visible while applying the calculation within each group. Use it when you want row-level data alongside group-level insights (like customer purchases with total spend).

Think of PARTITION BY as “grouping without losing detail.”

How do window functions impact performance?

Window functions can be resource-intensive because they require sorting and scanning large partitions, which increases memory and compute usage. To optimize performance, index the columns used in PARTITION BY and ORDER BY, limit partition sizes, and reuse common window definitions. When tuned properly, window functions often outperform complex joins or subqueries.

What are some common examples of SQL Analytic Functions?

• Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
• Window Aggregate Functions: SUM(), AVG(), COUNT(), MAX(), MIN() used with the OVER clause
• Lag/Lead Functions: LAG(), LEAD() to access values from preceding or succeeding rows
• Value Functions: FIRST_VALUE(), LAST_VALUE() to retrieve the first or last value in a window

Can Analytic Functions be nested?

You cannot directly nest analytic functions within the OVER clause of another analytic function. However, you can achieve similar results by using subqueries where one analytic function is computed in an inner query, and another analytic function is applied to the results of that subquery.

Mastering SQL analytic functions is just one step in becoming interview-ready. If you want to dive deeper into SQL concepts that show up repeatedly in analytics interviews, we’ve built practical guides that walk through examples with clear explanations.

Check out these related tutorials:

• SQL COUNT CASE WHEN – Learn how to count conditionally within your queries.
• SQL Conditional Join – Explore how to join tables based on conditional logic.
• SQL Cumulative Sum – Practice writing queries for running totals and moving sums.
• SQL Left Join Guide – Understand one of the most common join types with examples.

Practice Next

Ready to level up? Check out our SQL Interview Learning Path and practice with SQL Interview Questions designed to help you ace real data science and analytics interviews.

Interviewing at FAANG+ companies? Use our curated company specific guides (Meta SQL Guide, Amazon SQL Questions, Microsoft Interview Questions) to prep like the pros.