SQL Window Functions Made Easy: Your Guide to Running Totals, Rankings, and More

SQL window functions are a secret weapon for anyone working with data. They help you answer questions and find patterns that basic queries cannot. With window functions, you can calculate running totals, rankings, moving averages, and more—all while keeping every row in your result set.

In this guide, you'll discover how to use SQL window functions step-by-step. You'll see how to use them for real business needs such as finding the top orders for every customer, spotting trends over time, and summarizing key data points without sacrificing detail. By the end, you'll know how to spot the right tool for the job and write sharper SQL queries for smarter analysis.

What Are SQL Window Functions?

SQL window functions let you perform calculations across groups of rows that relate to the current row. Unlike standard aggregate functions, which reduce your results to a single line per group, window functions give you extra columns of insight—while keeping every row in your table.

You can use window functions for:

• Running totals: Keep a running sum as you move down the table.
• Rankings: Number or sort rows within groups (like customers or departments).
• Moving averages: Compare a row's value to others right beside it.

Think of a window function like a spreadsheet formula that "watches" the data in a window around each row. This window is defined by the OVER clause. For example, if you want to rank orders within each customer, your window starts over for every customer group.

Why use window functions?
They save you from writing subqueries or self-joins and help you do more in a single step. You don't lose details, which makes reports and dashboards easier to build.

Unpacking SQL's Ranking Functions

SQL offers several built-in functions that make ranking data simple and fast. Here are the main ones you need:

ROW_NUMBER(): Assigning Unique Ranks

The ROW_NUMBER() function gives a unique number to each row within a window. If you're looking at orders for each customer, it will number every order starting from 1 for each customer.

Example:
Suppose you want to list each customer's orders, with the most recent first.

SELECT
  customer_id,
  order_date,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank
FROM
  orders;

• Each customer's orders are listed, newest at the top, with order_rank starting at 1.
• Even if two orders share the same date, they still get different ranks.

Key benefit:
Always delivers unique rank values, no matter what.

RANK() and DENSE_RANK(): Handling Ties

These two functions come in handy when you want to assign the same rank to equal values (ties). Here's how they differ:

RANK()

• Assigns the same rank for tied values.
• Skips numbers after ties.

DENSE_RANK()

• Assigns the same rank for ties.
• Does not skip rank numbers.

Order Amounts	RANK()	DENSE_RANK()
100	1	1
100	1	1
90	3	2
80	4	3

Notice: After two orders tie for 100, RANK() jumps to 3 for the next value, while DENSE_RANK() goes to 2.

When to use these:

• Use RANK() if you want to reflect skipped places in competitions, such as sports rankings.
• Use DENSE_RANK() if you need continuous ranks even with ties, such as prize brackets.

Typical query:

SELECT
  customer_id,
  order_amount,
  RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS order_rank
FROM
  orders;

NTILE(): Splitting Results Into Groups

NTILE() divides your result set into a specified number of buckets, like quartiles or percentiles.

Use case:
To see which orders fall into the top 25% for each customer.

SELECT
  customer_id,
  order_amount,
  NTILE(4) OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS quartile
FROM
  orders;

• NTILE(4) splits orders into four groups (quartiles) within each customer.
• Each group contains, as closely as possible, the same number of rows.

Why it matters:
This is powerful for benchmarking, segmenting customers, or highlighting top performers in any list.

Running Totals and Moving Averages: Aggregate Functions With a Window

Aggregate functions like SUM() and AVG() get more powerful when combined with a window. Now, you can calculate a rolling summary that updates for each row.

Running Totals With SUM()

If you want to see the cumulative sales for each customer over time, use SUM() with a window that moves as you move down the table.

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

Breakdown:

• PARTITION BY customer_id restarts the total for each customer.
• ORDER BY order_date sorts the orders in time order.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW adds up all order amounts from the start through the current row.

You get a running total that updates for each order.

Moving Averages With AVG()

Want to smooth out seasonal sales spikes and focus on trends? Use a moving average.

SELECT
  customer_id,
  order_date,
  order_amount,
  AVG(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM
  orders;

Explanation:

• This shows the average order value over the current row and the two before it.
• Helps spot changes in buying behavior quickly.

Takeaway:
Window functions let you add up, average, or even count over specific "windows" of data—without hiding or grouping away any details.

Comparing Rows: LAG() and LEAD() for Previous and Next Values

Sometimes, you need to compare each row to the one before or after. The LAG() and LEAD() functions do exactly that.

LAG(): Seeing the Previous Row

LAG() fetches the value from the prior row. Great for calculating differences, tracking changes, or doing period-over-period analysis.

Example:
Find the difference in sales from the last order for each customer.

SELECT
  customer_id,
  order_date,
  order_amount,
  LAG(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_order_amount
FROM
  orders;

Use cases:

• Tracking customer growth from one period to another
• Spotting sudden jumps or drops

LEAD(): Peeking at the Next Row

LEAD() is the flip side, pulling the next row's value.

SELECT
  customer_id,
  order_date,
  order_amount,
  LEAD(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS next_order_amount
FROM
  orders;

Use cases:

• Planning forecasts by seeing what comes next
• Checking how soon someone bought again

Both functions make it easy to answer questions like "How much did a customer's spending increase since their last purchase?"

Finding First and Last Records Per Group: FIRST_VALUE() and LAST_VALUE()

There are times you need to grab just the first or last value from a set, but keep all the detail for every row. Enter FIRST_VALUE() and LAST_VALUE().

FIRST_VALUE(): Find the Earliest Entry

FIRST_VALUE() returns the first value in your defined window, such as the earliest order for every customer.

SELECT
  customer_id,
  order_date,
  order_amount,
  FIRST_VALUE(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_order_amount
FROM
  orders;

Usage:

• Compare current order to a customer's first order
• Identify onboarding patterns or changes since first purchase

LAST_VALUE(): Grabbing the Latest Record

LAST_VALUE() lets you get the last value for a group (like most recent purchase), but it comes with a catch:
You have to set the window frame just right to make sure you really get the last value in the group, not just up to the current row.

SELECT
  customer_id,
  order_date,
  order_amount,
  LAST_VALUE(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM
  orders;

Important:
Without the correct window frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), LAST_VALUE() may not behave as you want. Be explicit or you could get the wrong answer.

Benefits:

• Find each customer's most recent order instantly
• Fuel lifecycle analysis and retention programs

More Ways to Grow: Online Courses and Certifications

If you want to unlock bigger roles in data analytics or go beyond just basic SQL, structured learning can help fast-track your skills. Some solid options include the Google Data Analytics Professional Certificate and the Google Advanced Data Analytics Professional Certificate.

These programs help you build a strong foundation, working from SQL basics up to advanced techniques—including hands-on practice with window functions. For even more tutorials and tips, check out the Knowstar blog or browse hands-on SQL guides from trusted learning sites.

Wrapping Up

SQL window functions help you answer bigger questions with less work. You can track running totals, rank records, compare rows, and extract first or last entries, all while keeping your original data detail. With these tools, your queries stay powerful and your results are more actionable.

Start by adding one function—like ROW_NUMBER() or SUM() OVER—to your next SQL query. Watch how much more flexible and insightful your data analysis becomes. For more real-world SQL tips, subscribe to channels like Learn at Knowstar and explore their full SQL window functions video to see these concepts in action.

Stay curious, keep building your SQL skills, and soon you'll be solving the kinds of data puzzles others struggle with!