SQL Window Functions and Analytic Functions: The Complete Guide

Window functions compute across a set of rows related to the current row, without collapsing the result. Regular aggregates reduce many rows into one; window functions keep every row visible and add the computed value alongside.

-- Regular aggregate: one row back
SELECT SUM(order_amount) FROM orders;
-- Returns 76200

-- Window aggregate: every row back, total attached
SELECT order_id,
       order_amount,
       SUM(order_amount) OVER () AS total
FROM   orders;
-- Returns 7 rows, each with the same total

The single character difference — adding OVER () — changes the function from "collapse everything" to "compute and keep every row". That is the entire idea, and everything else about window functions is a refinement.

The practical difference shows up in reports. A regular aggregate gives you a single total for the whole table. A window aggregate gives you that total on every row, alongside the individual detail — which is what most reports actually need.

OVER defines the window — the set of rows the function gets to see when computing its value for the current row. Without OVER, a window function has no scope and the query fails.

-- Whole result set
SUM(order_amount) OVER ()

-- One window per customer
SUM(order_amount) OVER (PARTITION BY customer_id)

-- Running total within each customer's orders
SUM(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_id
)

The three forms above show the three common cases. No PARTITION BY means one window across the whole result. Adding PARTITION BY splits into one window per group. Adding ORDER BY makes the window grow as it moves through the sorted rows.

You can also define a frame inside OVER to control exactly which rows the current row can see — for example, "the two rows before this one and the current row" for a moving average. That is where window functions start getting precise.

ROW_NUMBER assigns a unique sequential number to each row in the window, starting at 1. Even identical rows get different numbers, because ROW_NUMBER does not care about ties.

SELECT order_id,
       order_amount,
       ROW_NUMBER() OVER (ORDER BY order_amount DESC) AS rn
FROM   orders;

On our data, order 1004 (22,000) gets row number 1, order 1006 (18,000) gets 2, order 1001 (12,000) gets 3, and so on down to order 1005 (3,200) at 7.

The classic use is top N per group. Wrap the query in a CTE and filter on the row number:

WITH ranked AS (
    SELECT c.customer_name,
           o.order_id,
           o.order_amount,
           ROW_NUMBER() OVER (
               PARTITION BY c.customer_id
               ORDER BY o.order_amount DESC
           ) AS rn
    FROM   customers c
    JOIN   orders o ON c.customer_id = o.customer_id
)
SELECT customer_name, order_id, order_amount
FROM   ranked
WHERE  rn = 1;

That returns the largest order for each customer. Change rn = 1 to rn <= 3 and you get the top three per customer. This pattern shows up in interviews constantly because it requires both window function syntax and the CTE or subquery wrapper.

Both RANK and DENSE_RANK rank rows by an ordering. The difference is what happens after a tie.

SELECT order_id,
       order_amount,
       RANK()       OVER (ORDER BY order_amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY order_amount DESC) AS dense_rnk
FROM   orders;

On our data, no two orders share an amount, so both functions produce the same numbers. To see the difference, add a row with amount 12,000 — duplicating order 1001.

Two rows tie at rank 3. With RANK, the next row gets rank 5 — the gap reflects that two positions were used. With DENSE_RANK, the next row gets rank 4 — the ranks are consecutive with no gaps.

The choice depends on what the number means. For Olympic-style ranking, RANK matches expectation — gold, silver, bronze with no skipped places. For "how many distinct tiers exist", DENSE_RANK matches expectation — after two tied third-place finishers, the next is fourth.

LEAD and LAG give you access to a value from a subsequent or preceding row in the same window. They let you compare a row with its neighbours without a self-join.

SELECT order_id,
       order_amount,
       LAG(order_amount)  OVER (ORDER BY order_id) AS previous_order,
       LEAD(order_amount) OVER (ORDER BY order_id) AS next_order
FROM   orders;

Order 1001 (12,000) shows NULL as its previous value because there is no row before it, and 4,500 as its next value. Order 1002 (4,500) shows 12,000 as previous and 7,500 as next. The pattern continues down the list, with order 1007 seeing 18,000 as its previous and NULL as its next.

The classic use is period-over-period comparison. Monthly growth, daily change, year-over-year delta — all of them come from LAG or LEAD on the appropriate time column.

SELECT order_id,
       order_amount,
       order_amount - LAG(order_amount) OVER (ORDER BY order_id) AS change
FROM   orders;

Both functions accept two optional arguments: the offset (how many rows back or forward) and a default value for out-of-range lookups. LAG(order_amount, 2, 0) looks two rows back and returns 0 instead of NULL when there is no such row.

NTILE divides the rows into equal-sized buckets and assigns a bucket number. PERCENT_RANK returns the relative rank of a row as a value between 0 and 1. Both describe where a row sits in the distribution, but they express it differently.

-- NTILE: divide into quartiles
SELECT order_id,
       NTILE(4) OVER (ORDER BY order_amount DESC) AS quartile
FROM   orders;

-- PERCENT_RANK: value between 0 and 1
SELECT order_id,
       PERCENT_RANK() OVER (ORDER BY order_amount) AS pct_rank
FROM   orders;

On our data with seven orders, NTILE(4) puts them into quartiles. The first quartile (top 25%) gets bucket 1, the next gets 2, and so on. The last bucket may have fewer rows if 7 does not divide evenly by 4.

PERCENT_RANK computes (rank - 1) / (total_rows - 1). The lowest value in the sorted order gets 0, the highest gets 1.0. Everything else falls in between as a decimal.

The reason to use NTILE is when you want discrete buckets — "top quartile", "middle 50%", "bottom quartile". The reason to use PERCENT_RANK is when you want a continuous position — "this row is at the 78th percentile". They answer slightly different questions.

Both SUM and AVG work as window functions when you add an OVER clause. The behaviour — whether they produce a running total, a moving average, or a per-group value — depends on what the OVER clause contains.

SELECT order_id,
       order_amount,
       SUM(order_amount) OVER (ORDER BY order_id) AS running_total,
       AVG(order_amount) OVER (
           ORDER BY order_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM   orders;

The first window function adds an ORDER BY with no frame, so the default frame applies — everything up to the current row. That produces a running total: 12,000, 16,500, 24,000, 46,000, 49,200, 67,200, 76,200.

The second window function has an explicit frame — two rows before plus the current row. That produces a moving average of three consecutive orders. Order 1001 is 12,000 (only itself). Order 1002 is 8,250 (average of 12,000 and 4,500). Order 1003 is 8,000 (average of 12,000, 4,500, and 7,500). And so on.

The pattern generalises. Change the frame to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW and you get a seven-row moving average. Add PARTITION BY customer_id and each customer gets their own running total and moving average, computed independently.

A window frame is the specific set of rows within the partition that the window function sees for the current row. You define it with a ROWS or RANGE clause inside the OVER clause.

SELECT order_id,
       order_amount,
       SUM(order_amount) OVER (
           ORDER BY order_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM   orders;

That frame says: from the start of the partition all the way to the current row. The result is a running total, because the frame grows as the window moves.

The boundaries you can use:

• UNBOUNDED PRECEDING — start of the partition
• N PRECEDING — N rows before the current row
• CURRENT ROW — the current row itself
• N FOLLOWING — N rows after the current row
• UNBOUNDED FOLLOWING — end of the partition

The default frame, when ORDER BY is present, is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is why a simple SUM(x) OVER (ORDER BY y) gives a running total.

ROWS counts physical rows — "the two rows before". RANGE counts by value — "all rows whose ordering value is within two of mine". ROWS is what you want 95% of the time. RANGE is useful when the ordering column has ties and you want them treated as a single unit.

Five patterns show up again and again. Each replaces a self-join or correlated subquery that used to be the only way to solve the problem.

Ranking within groups. Top three salespeople per region, highest-paid employee per department, most-ordered product per category. Wrap the query in a CTE and filter on the row number.

WITH ranked AS (
    SELECT region, salesperson_name, sales_amount,
           ROW_NUMBER() OVER (
               PARTITION BY region ORDER BY sales_amount DESC
           ) AS rn
    FROM   sales
)
SELECT region, salesperson_name, sales_amount
FROM   ranked
WHERE  rn <= 3;

Running totals. Year-to-date revenue, cumulative signups, rolling inventory. Add an ORDER BY inside OVER and the window grows as the query moves through the data.

Moving averages. Seven-day or 30-day rolling metrics for smoothing out daily noise. Use a ROWS BETWEEN frame to define the window size.

Period-over-period comparisons. Month-over-month growth, day-over-day change, year-over-year delta. LAG on the time column gets you the previous period's value, and the difference is your growth.

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM   monthly_revenue;

Deduplication. Find and remove duplicate rows while keeping one per key. ROW_NUMBER partitioned by the key columns, keeping the row with rn = 1.

Window functions require sorting, and sorting is usually the expensive part. Four things help.

Index the partition and order columns. When the database can read rows in sorted order, it skips the sort step entirely.

CREATE INDEX idx_orders_customer_amount
ON orders (customer_id, order_amount DESC);

Filter before the window function runs. A tight WHERE clause reduces the row count that has to be sorted. On large tables, this is often a bigger win than indexing.

SELECT order_id,
       SUM(order_amount) OVER (ORDER BY order_id) AS running_total
FROM   orders
WHERE  order_status = 'Delivered';

Reuse the same window definition. Multiple window functions with the same PARTITION BY and ORDER BY share a single sort. Different windows require different sorts.

Check the execution plan. Run EXPLAIN and look for the window function node. If it is processing more rows than expected, the filter above it is not being pushed down. Moving the filter into the subquery or the outer query usually fixes it.

One more thing: window functions on very large tables can spill to disk if the sort does not fit in memory. Increasing the database's sort memory setting can help, but reducing the row count is usually the better fix.

No. Window functions are evaluated after WHERE, so the WHERE clause cannot see their results. Trying to filter on them directly produces an error or returns the wrong rows.

-- This fails
SELECT order_id,
       ROW_NUMBER() OVER (ORDER BY order_amount) AS rn
FROM   orders
WHERE  rn = 1;
-- ERROR: column "rn" does not exist

The fix is to wrap the query in a subquery or CTE and filter the outer query:

WITH ranked AS (
    SELECT order_id,
           order_amount,
           ROW_NUMBER() OVER (ORDER BY order_amount DESC) AS rn
    FROM   orders
)
SELECT order_id, order_amount
FROM   ranked
WHERE  rn = 1;

That is the standard pattern for top N per group and every other filter on a window function's result. It works on every database.

Some databases — Snowflake, BigQuery, Teradata — support a QUALIFY clause that filters on window function results without wrapping. Same result, shorter query:

SELECT order_id,
       ROW_NUMBER() OVER (ORDER BY order_amount DESC) AS rn
FROM   orders
QUALIFY rn = 1;

If you are on one of those databases, QUALIFY is cleaner. If not, the CTE wrapper is what you reach for.

Usually not directly. Most databases refuse to combine DISTINCT with window functions in the same SELECT, because the semantics conflict — DISTINCT wants to collapse rows, and window functions want to preserve them.

-- This fails on most databases
SELECT DISTINCT customer_id,
       SUM(order_amount) OVER (PARTITION BY customer_id)
FROM   orders;
-- ERROR: DISTINCT and window functions cannot be used together

The workaround is to compute the window function in a subquery and apply DISTINCT in the outer query:

SELECT DISTINCT customer_id, customer_total
FROM  (
    SELECT customer_id,
           SUM(order_amount) OVER (PARTITION BY customer_id) AS customer_total
    FROM   orders
) sub;

That produces one row per customer, with each customer's total spend. If you already have the window function and only want the distinct values, this is the way to get there.

There is usually a cleaner formulation. When you find yourself combining DISTINCT with a window function, the question is often "one row per group with a group-level calculation". That is what GROUP BY is for:

SELECT customer_id, SUM(order_amount) AS customer_total
FROM   orders
GROUP BY customer_id;

Same result, no window function needed. The window function version is only necessary when the individual rows matter — say, when you want to show each order alongside the customer total, and also a distinct list of customers. In those cases, splitting the query into two parts is usually clearer than trying to combine DISTINCT with OVER.

ROWS counts physical rows. RANGE counts by value. The distinction only matters when the ordering column has ties.

-- ROWS: exactly 3 physical rows per frame
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- RANGE: every row whose value is within 2 of the current
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

Consider a table ordered by score, with these values: 10, 20, 20, 20, 30, 40. For the second 20 in the list:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes exactly three rows — the two physical rows before it plus itself — which is 10, 20, 20.
• RANGE BETWEEN 2 PRECEDING AND CURRENT ROW includes every row whose score is between 18 and 20. That excludes the 10 and includes all three 20s.

The difference is subtle but real. With ROWS, the frame is always the same size in terms of row count. With RANGE, the frame size depends on how many rows share the current value.

In practice, ROWS is what you want 95% of the time. Moving averages, running totals, and lookback windows are all about counting rows, not matching values. RANGE comes up when you want "all rows within a window of time" or "all rows tied with the current row" — both of which are less common.

Note that the default frame when ORDER BY is present is RANGE UNBOUNDED PRECEDING AND CURRENT ROW on most databases. That is why a running total with ties can behave differently than expected — the default RANGE includes all rows tied with the current one, not just the ones physically before it. Writing the frame explicitly with ROWS avoids the surprise.

Window functions treat NULL the same way regular aggregates do — as unknown values that are skipped by SUM, AVG, MIN, and MAX, and counted by COUNT(*).

SELECT order_id,
       order_amount,
       SUM(order_amount) OVER (ORDER BY order_id) AS running_total,
       COUNT(order_amount) OVER (ORDER BY order_id) AS non_null_count
FROM   orders;

If an order had a NULL order_amount, it would not contribute to the running total, and COUNT(order_amount) would not count it. But COUNT(*) would — it counts rows, not values.

Ranking functions handle NULLs differently depending on the database. Oracle and PostgreSQL sort NULLs last by default in ascending order, so a NULL column value would get the highest row number. MySQL and SQL Server sort them first. If you care about where NULLs land in a ranking, be explicit with NULLS FIRST or NULLS LAST in the ORDER BY.

SELECT customer_name,
       credit_limit,
       RANK() OVER (ORDER BY credit_limit DESC NULLS LAST) AS rnk
FROM   customers;

Fiona Mart, whose credit limit is NULL, would get the lowest rank (or be excluded, depending on the outer filter) because her NULL sorts last in descending order.

LAG and LEAD return NULL when the requested row does not exist (first or last row of the partition). If that breaks downstream arithmetic, pass a default value as the third argument:

LAG(order_amount, 1, 0) OVER (ORDER BY order_id) AS previous_or_zero

That returns 0 instead of NULL for the first row, so any subtraction or addition downstream works without special handling.

Yes, but they operate at different stages. GROUP BY runs first and collapses rows. The window function then operates over the grouped result.

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

That query groups orders by customer, computes the total spend, then ranks the customers by that total. The window function sees the grouped result — one row per customer — not the original orders.

The order matters. Inside a window function, you cannot reference columns that were eliminated by the GROUP BY. Writing RANK() OVER (ORDER BY order_amount) in the query above would fail because order_amount is not part of the grouped result — only its aggregate is.

You cannot use a window function inside an aggregate either. Something like SUM(ROW_NUMBER() OVER (ORDER BY x)) is not valid syntax. The two features operate at different levels of the query, and combining them directly is not allowed.

The pattern that does work is what is shown above: aggregate first with GROUP BY, then rank or window over the aggregated rows. Any window function that operates on top of a GROUP BY result uses this shape.

The standard recipe uses ROW_NUMBER in a CTE, then filters the outer query on the row number.

WITH ranked AS (
    SELECT c.customer_name,
           o.order_id,
           o.order_amount,
           ROW_NUMBER() OVER (
               PARTITION BY c.customer_id
               ORDER BY o.order_amount DESC
           ) AS rn
    FROM   customers c
    JOIN   orders o ON c.customer_id = o.customer_id
)
SELECT customer_name, order_id, order_amount
FROM   ranked
WHERE  rn <= 2;

On our data, that returns each customer's top two orders. Alice Corp gets her 12,000 and 9,000 orders. Bob Traders gets 4,500 and 3,200. Charlie Ltd and Diana Retail each have only one order, so both of theirs come back. Total: six rows.

To use RANK or DENSE_RANK instead of ROW_NUMBER, change the function name and the filter. With RANK, rn <= 2 includes ties — if two orders tie for second place, both come back. With ROW_NUMBER, only one of them comes back, chosen arbitrarily.

-- ROW_NUMBER: exactly 2 rows per customer
ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY o.order_amount DESC) AS rn
WHERE rn <= 2

-- RANK: up to 2 ranks, but ties can exceed 2 rows
RANK() OVER (PARTITION BY c.customer_id ORDER BY o.order_amount DESC) AS rnk
WHERE rnk <= 2

The choice depends on what you mean by "top two". Exactly two rows per group means ROW_NUMBER. The top two positions, ties included, means RANK.

FIRST_VALUE returns the value from the first row of the window. LAST_VALUE returns the value from the last row.

SELECT order_id,
       order_amount,
       FIRST_VALUE(order_amount) OVER (
           ORDER BY order_id
       ) AS first_order,
       LAST_VALUE(order_amount) OVER (
           ORDER BY order_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_order
FROM   orders;

FIRST_VALUE gives every row the same value — 12,000, the first order's amount. That is often useful for "show each row next to the starting value" comparisons.

LAST_VALUE is trickier. The default frame (from partition start to current row) means LAST_VALUE gives the current row's own value, not the partition's last value. To get the actual last row's value, you have to specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING explicitly. That is one of the most common bugs with window functions.

On our data with the explicit frame, LAST_VALUE returns 9,000 — the last order's amount — on every row. Without the explicit frame, it would return each order's own amount, which is the same as just selecting the column directly.

Both functions accept optional PARTITION BY and can be combined with frames. A useful pattern is FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) — every order shows the date of the customer's first order.

For the same result, GROUP BY is usually faster. It collapses rows early, which reduces the amount of data the rest of the query has to process. Window functions keep every row, so they often sort more data.

-- GROUP BY: collapses to one row per customer
SELECT customer_id, SUM(order_amount)
FROM   orders
GROUP BY customer_id;

-- Window function: keeps every order row
SELECT customer_id,
       order_id,
       SUM(order_amount) OVER (PARTITION BY customer_id)
FROM   orders;

The first query returns four rows (one per customer with orders). The second returns seven (one per order). The GROUP BY version sorts four rows; the window version sorts seven.

The performance gap widens with scale. On a table with millions of rows grouped into a few thousand groups, GROUP BY collapses early and sorts a few thousand rows. A window function on the same data sorts millions of rows to produce the same information alongside each detail row.

That said, they answer different questions. If you only need the summary, use GROUP BY. If you need the summary attached to every detail row, you need a window function. The performance cost is the price of keeping the details.

When both are options — say, "show customers with total spend over 20,000" — comparing the two plans is worth doing. Sometimes the window function version is faster because it avoids a self-join. Sometimes the GROUP BY version is faster because it collapses early. It depends on the query, the data, and the indexes.

No, not directly. Window functions cannot nest inside each other, and one window function cannot reference the result of another in the same SELECT list.

-- This fails
SELECT order_id,
       SUM(order_amount) OVER (ORDER BY order_id) AS running_total,
       RANK() OVER (ORDER BY SUM(order_amount) OVER (ORDER BY order_id)) AS rnk
FROM   orders;
-- ERROR: window functions are not allowed here

The workaround is a CTE or subquery — compute the first window function in an inner query, then compute the second in an outer query that can see the result.

WITH totals AS (
    SELECT order_id,
           order_amount,
           SUM(order_amount) OVER (ORDER BY order_id) AS running_total
    FROM   orders
)
SELECT order_id,
       running_total,
       RANK() OVER (ORDER BY running_total DESC) AS running_rank
FROM   totals;

That computes the running total in the inner query, then ranks by running total in the outer query. Two stages, two different window functions.

The same pattern applies when you want to filter on a window function result. You cannot WHERE running_total > 50000 in the same query, but you can in a wrapping query.

Some databases allow LATERAL joins or specific syntax extensions that make nesting possible, but the general rule is that window functions are computed at one level and their results are only available to wrapping queries. Two-level CTEs handle almost every case.

Almost every modern relational database supports them. The coverage is close to universal now, but there are a few version-specific notes worth knowing.

• Oracle — has supported window functions since version 8i (1999). The syntax is essentially the standard one.
• SQL Server — supported since SQL Server 2005. The syntax is standard, and SQL Server also supports a few platform-specific extensions.
• PostgreSQL — supported since version 8.4 (2009). Follows the standard.
• MySQL — added support in version 8.0 (2018). If you are on MySQL 5.7 or earlier, they are not available.
• SQLite — added support in version 3.25 (2018).
• MariaDB — added support in version 10.2 (2017).
• Snowflake, BigQuery, Redshift, Databricks — all cloud warehouses support window functions natively.

If you are on MySQL 5.7 or older, or an older version of MariaDB, you need to simulate window functions with correlated subqueries or self-joins. That is noticeably more work and often slower — one of the reasons upgrading to MySQL 8.0 is worth it for analytics workloads.

The specific functions available vary slightly. ROW_NUMBER, RANK, DENSE_RANK, SUM, AVG, COUNT, MIN, MAX, LAG, LEAD, and FIRST_VALUE are supported almost everywhere. Less common functions like PERCENTILE_CONT or PERCENTILE_DISC have narrower support — check your specific database before relying on them.

Both can compute per-row values based on other rows. They differ in how they express the calculation and how the database executes them.

A correlated subquery runs once per outer row. For a table with a million rows, that means a million subquery executions. A window function computes across all rows at once, in a single pass.

-- Correlated subquery: runs once per customer
SELECT c.customer_name,
       (SELECT SUM(o.order_amount)
        FROM   orders o
        WHERE  o.customer_id = c.customer_id) AS total_spend
FROM   customers c;

-- Window function: runs once for the whole query
SELECT c.customer_name,
       SUM(o.order_amount) OVER (PARTITION BY c.customer_id) AS total_spend
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Same output, different execution strategy. The window function version is usually much faster on large tables because the database only has to sort once.

There is a subtle difference in the output when a customer has no orders. The correlated subquery returns NULL for that customer. The window function with a LEFT JOIN returns the customer row once for each order (or one row with NULL order amount if there are no orders). If you want one row per customer, use a subquery or a GROUP BY. If you want one row per order with the customer total beside it, use the window function.

The general guidance: use window functions when you want to keep every detail row. Use correlated subqueries or aggregates when you are collapsing rows anyway. Window functions are almost always faster for the same output on modern databases.

ROW_NUMBER assigns unique numbers even when values tie. That means the tie must be broken somehow — the database picks an arbitrary order, or you give it a tie-breaker column.

-- Arbitrary tie-breaking (risky)
ROW_NUMBER() OVER (ORDER BY order_amount DESC)

-- Explicit tie-breaking (predictable)
ROW_NUMBER() OVER (ORDER BY order_amount DESC, order_id ASC)

The first version produces different results on different runs if two orders have the same amount. The database is free to pick whichever order it reaches first. That is fine for exploratory queries but a problem for reports that need to be reproducible.

Adding a unique tie-breaker column — usually the primary key — makes the ordering deterministic. On our data, two orders with the same amount would be ordered by order_id ascending, so the one with the lower id always gets the lower row number.

If you do not want to break ties arbitrarily — you want tied rows to share a rank — use RANK or DENSE_RANK instead. RANK gives the same rank to tied rows and leaves gaps in the next rank. DENSE_RANK gives the same rank and does not leave gaps.

The choice between the three functions reflects what "rank" means for the question. Exactly one row per position means ROW_NUMBER. Positions with ties mean RANK or DENSE_RANK, depending on whether subsequent positions skip numbers.

Divide the row's value by the sum of the whole partition, and multiply by 100. The window function provides the denominator.

SELECT order_id,
       order_amount,
       ROUND(
           order_amount * 100.0 / SUM(order_amount) OVER (),
           2
       ) AS pct_of_total
FROM   orders;

That computes each order's share of the 76,200 grand total. Order 1004 (22,000) is about 28.87%. Order 1002 (4,500) is about 5.91%. Every row shows its own share of the whole.

For percent within a group, add PARTITION BY:

SELECT customer_id,
       order_id,
       order_amount,
       ROUND(
           order_amount * 100.0 /
           SUM(order_amount) OVER (PARTITION BY customer_id),
           2
       ) AS pct_of_customer
FROM   orders;

Alice Corp's orders each show their share of her 28,500 total. Order 1001 (12,000) is 42.11%. Order 1003 (7,500) is 26.32%. Order 1007 (9,000) is 31.58%.

The * 100.0 is important — without it, integer division truncates the result. Writing 100 instead of 100.0 would give 0 for most rows because the integer division of 4,500 by 76,200 is 0. Always use a decimal multiplier.

The same pattern works for running percentage of total — replace SUM(order_amount) OVER () with SUM(order_amount) OVER (ORDER BY order_id). Each row then shows what percent of the grand total has been accumulated up to that row.

A self-join joins a table to itself and combines rows based on a matching condition. A window function computes across related rows without any join at all. They can sometimes produce the same result, but the window function is almost always cleaner and faster.

-- Self-join: compare each order to the previous one
SELECT o1.order_id,
       o1.order_amount,
       o2.order_amount AS previous_order
FROM   orders o1
LEFT JOIN orders o2 ON o1.order_id = o2.order_id + 1;

-- Window function: same result, no join
SELECT order_id,
       order_amount,
       LAG(order_amount) OVER (ORDER BY order_id) AS previous_order
FROM   orders;

Both queries produce the same output. The window function is shorter, does not require the assumption that order ids are consecutive, and typically runs faster because the database only needs one sort instead of a self-join.

The self-join has one advantage: it works in databases that do not support window functions, like MySQL 5.7 or older. If you are on one of those, the self-join is the workaround.

Beyond simple comparisons, window functions do things self-joins cannot. A running total, a moving average, a rank within a group — none of those are natural to express as a self-join. Before window functions existed, people wrote them using correlated subqueries, which were much slower.

The general rule: if a window function can express what you want, use it. Reach for the self-join only when you need functionality window functions do not provide — usually when you want to combine columns from two different rows into one row in a way that does not fit the window frame model.

This is one of the most common window function interview questions, and there are two reasonable answers depending on whether ties matter.

-- If there are no ties, or you want strictly the 2nd row
WITH ranked AS (
    SELECT department_id,
           employee_name,
           salary,
           ROW_NUMBER() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS rn
    FROM   employees
)
SELECT department_id, employee_name, salary
FROM   ranked
WHERE  rn = 2;

That gives exactly one row per department — the employee whose salary ranks second by ROW_NUMBER. If two employees tie for the highest salary, one of them gets rn = 1 and the other gets rn = 2, so the "second row" is one of the tied top earners, not the actual second-highest value. That is usually not what the interviewer wants.

-- If ties should be excluded, use DENSE_RANK
WITH ranked AS (
    SELECT department_id,
           employee_name,
           salary,
           DENSE_RANK() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS dr
    FROM   employees
)
SELECT department_id, employee_name, salary
FROM   ranked
WHERE  dr = 2;

With DENSE_RANK, tied top salaries both get rank 1, and the next distinct salary gets rank 2. That matches the plain-English meaning of "the second-highest salary" better.

Which one you write depends on the interviewer's intent. A good answer mentions both: ROW_NUMBER if you want exactly one row per department, DENSE_RANK if you want the second distinct salary tier, RANK if you want ties to share ranks but skip the next position. Asking which they mean is a sign of thoroughness, not uncertainty.

The same pattern solves a family of related questions. Third-highest salary per department: change the filter to rn = 3 or dr = 3. Highest salary per department: rn = 1 or dr = 1. Top three salaries per department: rn <= 3 or dr <= 3. Once the recipe is in your head, the variations are small edits.