Introduction
SQL is often introduced as a language for retrieving data, but for data scientists it is much more than that. It is a powerful analytical tool for transforming raw event data into metrics, comparisons, rankings, cohorts, trends, and features for downstream modeling. Two of the most important tools in that analytical toolkit are aggregate functions and window functions.
Aggregate functions reduce rows into summaries. Window functions calculate summaries while preserving the original rows.
That distinction sounds small, but it changes how you design queries, interpret results, and avoid analytical mistakes.
This article explains the difference, using practical examples and discussing when each approach is appropriate.
Aggregate functions: turning many rows into fewer rows
Aggregate functions summarize multiple rows into a single value. Common examples include:
SUM()
AVG()
COUNT()
MIN()
MAX()
Suppose you have a table called orders:
| order_id | customer_id | order_date | category | revenue |
|---|---|---|---|---|
| 1 | 101 | 2026-01-01 | Electronics | 500 |
| 2 | 101 | 2026-01-05 | Books | 30 |
| 3 | 102 | 2026-01-03 | Electronics | 200 |
| 4 | 103 | 2026-01-04 | Books | 45 |
| 5 | 102 | 2026-01-06 | Books | 25 |
If you want the total revenue across every order, an aggregate query is appropriate:
SELECT
SUM(revenue) AS total_revenue
FROM orders;
The result is one row:
| total_revenue |
|---|
| 800 |
The original five order rows have been collapsed into one summary row.
More often, analysts aggregate by a dimension. For example, to calculate revenue by product category:
SELECT
category,
SUM(revenue) AS total_revenue,
AVG(revenue) AS average_order_value,
COUNT(*) AS order_count
FROM orders
GROUP BY category;
This returns one row for each category:
| category | total_revenue | average_order_value | order_count |
|---|---|---|---|
| Books | 100 | 33.33 | 3 |
| Electronics | 700 | 350.00 | 2 |
This is the core behavior of aggregation: the GROUP BY clause establishes the grain of the output.
Before the query, the data was at the order level. After the query, it is at the category level.
For data analysis, this concept is essential. Every table and query has a level of detail:
- One row per event
- One row per order
- One row per customer
- One row per day
- One row per product category
- One row per customer-month
Aggregate functions intentionally change that level of detail. This is useful when the business question is itself summarized:
- What was total revenue last month?
- How many active users did each country have?
- Which product category has the highest sales?
In all these cases, the desired output is a summary table, not a record-level dataset.
The limitation of aggregation
The challenge appears when you want both the original row-level data and a summary statistic.
Imagine you want to look at every individual order while also seeing the customer’s lifetime spending. An ordinary aggregate query cannot do both in one straightforward result.
For example:
SELECT
customer_id,
SUM(revenue) AS customer_lifetime_revenue
FROM orders
GROUP BY customer_id;
This correctly calculates customer-level revenue:
| customer_id | customer_lifetime_revenue |
|---|---|
| 101 | 530 |
| 102 | 225 |
| 103 | 45 |
But all order-level information has disappeared. You can no longer see the individual purchases, dates, or categories associated with each customer.
A common workaround is to write an aggregate query in a common table expression or subquery and join it back to the original table:
WITH customer_totals AS (
SELECT
customer_id,
SUM(revenue) AS customer_lifetime_revenue
FROM orders
GROUP BY customer_id
)
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.category,
o.revenue,
ct.customer_lifetime_revenue
FROM orders AS o
JOIN customer_totals AS ct
ON o.customer_id = ct.customer_id;
This works, but it is more redundant than necessary. It also becomes cumbersome when you need several related metrics: customer totals, category averages, rank within country, previous purchase values, rolling averages, and so on.
This is where window functions become invaluable.
Window functions: calculations without collapsing rows
A window function performs a calculation across a related set of rows while retaining every row in the result.
The general syntax looks like this:
function_name(...) OVER (
PARTITION BY ...
ORDER BY ...
ROWS BETWEEN ...
)
The OVER() clause turns a normal function into a window function.
Here is the earlier customer lifetime revenue example, now written with a window function:
SELECT
order_id,
customer_id,
order_date,
category,
revenue,
SUM(revenue) OVER (
PARTITION BY customer_id
) AS customer_lifetime_revenue
FROM orders;
The result keeps all order-level records:
| order_id | customer_id | order_date | category | revenue | customer_lifetime_revenue |
|---|---|---|---|---|---|
| 1 | 101 | 2026-01-01 | Electronics | 500 | 530 |
| 2 | 101 | 2026-01-05 | Books | 30 | 530 |
| 3 | 102 | 2026-01-03 | Electronics | 200 | 225 |
| 4 | 103 | 2026-01-04 | Books | 45 | 45 |
| 5 | 102 | 2026-01-06 | Books | 25 | 225 |
Each order remains visible, but each one now carries an additional feature: the customer’s total spending.
From a data science standpoint, this is powerful because it allows you to add contextual variables without destroying the original dataset grain. These values can support exploratory analysis, customer segmentation, anomaly detection, feature engineering, and model preparation.
The most important distinction: output grain
The easiest way to remember the difference is this:
| Function type | What happens to rows? | Typical use |
|---|---|---|
| Aggregate function | Rows are collapsed into grouped summaries | Reporting and high-level metrics |
| Window function | Rows remain; calculations are added alongside them | Comparison, ranking, time-series features, row-level analysis |
Consider a question such as: “What percentage of category revenue did each order represent?”
You need the individual order revenue, but you also need total revenue for the order’s category. If you use an aggregate query alone, you lose the individual order values. A window function is ideal:
SELECT
order_id,
category,
revenue,
SUM(revenue) OVER (
PARTITION BY category
) AS category_revenue,
100.0 * revenue /
SUM(revenue) OVER (
PARTITION BY category
) AS percent_of_category_revenue
FROM orders;
For the Electronics category, the two orders contribute to a total of 700. Each row can now be interpreted relative to its category-level context.
This is a recurring pattern in analytical work: retain the observation, then attach the benchmark.
PARTITION BY: defining the comparison group
The PARTITION BY clause divides rows into logical groups for the window calculation.
For example:
AVG(revenue) OVER (
PARTITION BY category
)
This calculates the average order revenue within each category.
If PARTITION BY is omitted, the window includes all rows returned by the query:
AVG(revenue) OVER () AS overall_average_revenue
That allows direct comparisons between each row and the global average:
SELECT
order_id,
customer_id,
revenue,
AVG(revenue) OVER () AS overall_average_revenue,
revenue - AVG(revenue) OVER () AS difference_from_average
FROM orders;
This can be useful for finding unusually large or small transactions.
In practice, data scientists frequently partition by:
- Customer ID for customer behavior analysis
- User ID for product analytics
- Country, region, or market for geographic comparison
- Product category for merchandising analysis
- Experiment group for A/B test reporting
- Account ID for B2B usage behavior
- Month or week for period-based comparisons
The choice of partition is analytical, not merely technical. It defines the peer group against which a row is being evaluated.
ORDER BY: making the window sequential
Aggregate functions generally do not care about row order. The sum of sales is the same whether Monday’s data appears before Friday’s.
Window functions often do care about order, especially for time-based analysis.
Suppose you want each customer’s running total spending over time:
SELECT
customer_id,
order_date,
revenue,
SUM(revenue) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_customer_revenue
FROM orders;
For customer 101, the results would look like this:
| customer_id | order_date | revenue | running_customer_revenue |
|---|---|---|---|
| 101 | 2026-01-01 | 500 | 500 |
| 101 | 2026-01-05 | 30 | 530 |
The window is no longer the entire customer history at once. It expands through ordered rows.
This is especially useful for:
- Cumulative revenue
- Cumulative sign-ups
- Cumulative conversion counts
- Running account balances
- User engagement over time
- Inventory movement
- Daily active user trends
- Progress toward monthly targets
When multiple events can occur at the same timestamp or date, add a tie-breaker to ensure deterministic ordering:
ORDER BY order_date, order_id
Without a stable ordering rule, running totals and row sequence functions can produce ambiguous results.
Window frames: controlling which rows are included
A window frame refines the set of rows considered around the current row.
For example, a rolling three-day average might be written as:
AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3_day_average
This calculates an average using the current row and the two prior rows.
Frames are especially valuable in time-series analysis. Common examples include:
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
for a seven-row rolling average, or:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
for a running total from the start of the partition.
However, one should be careful with the difference between row-based and time-based frames. A frame of “six preceding rows” is not always equivalent to “the previous six calendar days.” If dates are missing, there may be fewer than seven days represented. Some SQL systems support range-based or interval-based frames, while others require generating a complete date spine first.
This distinction matters a great deal in production metrics. A “seven-day moving average” should normally mean seven calendar days, not simply seven available observations.
Ranking functions: a major window-function use case
Ranking is one of the clearest examples of a task that window functions solve elegantly.
Suppose you want to rank customers by their total spending:
SELECT
customer_id,
SUM(revenue) AS customer_revenue,
RANK() OVER (
ORDER BY SUM(revenue) DESC
) AS revenue_rank
FROM orders
GROUP BY customer_id;
Here, aggregate and window functions work together. The query first produces one row per customer with SUM(revenue). The window function then ranks those customer-level rows.
You might use:
RANK()
DENSE_RANK()
ROW_NUMBER()
NTILE()
The differences matter:
-
ROW_NUMBER()gives every row a unique sequential number. -
RANK()gives tied rows the same rank and leaves gaps afterward. -
DENSE_RANK()gives tied rows the same rank but does not leave gaps. -
NTILE(n)divides ordered rows into approximately equal-sized buckets.
For example, NTILE(10) is frequently used to assign customers to deciles based on lifetime value, engagement, risk score, or predicted propensity.
A practical customer segmentation query could look like this:
WITH customer_metrics AS (
SELECT
customer_id,
SUM(revenue) AS lifetime_value,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
lifetime_value,
order_count,
NTILE(4) OVER (
ORDER BY lifetime_value DESC
) AS value_quartile
FROM customer_metrics;
This produces a straightforward first-pass segmentation of customers into value quartiles.
LAG and LEAD: comparing rows across time
Window functions also enable comparisons between one row and nearby rows. LAG() retrieves a previous row’s value; LEAD() retrieves a later row’s value.
For a daily revenue table:
SELECT
order_date,
daily_revenue,
LAG(daily_revenue) OVER (
ORDER BY order_date
) AS previous_day_revenue,
daily_revenue -
LAG(daily_revenue) OVER (
ORDER BY order_date
) AS day_over_day_change
FROM daily_sales;
This lets you calculate changes over time without a self-join.
You can also calculate percentage change:
SELECT
order_date,
daily_revenue,
100.0 * (
daily_revenue -
LAG(daily_revenue) OVER (ORDER BY order_date)
) /
NULLIF(
LAG(daily_revenue) OVER (ORDER BY order_date),
0
) AS day_over_day_growth_pct
FROM daily_sales;
NULLIF prevents a division-by-zero error.
For customer behavior, LAG() can help answer questions such as:
- How many days passed between purchases?
- Was this customer’s order larger than their previous order?
- Has user activity increased or declined?
- Did a customer upgrade or downgrade their plan?
- Did a product’s weekly demand change materially?
Example:
SELECT
customer_id,
order_date,
revenue,
LAG(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS previous_order_date
FROM orders;
You can then calculate purchase intervals and use them in recency-frequency-monetary analysis, churn modeling, or customer lifecycle reporting.
Aggregates and windows often work best together
The question should not be “Which one should I use?” in an absolute sense. Mature analytical queries often use both.
For instance, imagine you want to calculate each category’s monthly sales, then determine each category’s share of total monthly sales:
WITH monthly_category_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
category,
SUM(revenue) AS category_revenue
FROM orders
GROUP BY
DATE_TRUNC('month', order_date),
category
)
SELECT
month,
category,
category_revenue,
SUM(category_revenue) OVER (
PARTITION BY month
) AS total_monthly_revenue,
100.0 * category_revenue /
SUM(category_revenue) OVER (
PARTITION BY month
) AS monthly_revenue_share
FROM monthly_category_sales;
The aggregate stage establishes the desired analytical grain: one row per category per month. The window stage adds context: each category’s contribution to the month’s total.
This two-stage pattern is extremely common in analytics engineering and data science:
- Aggregate raw event data to the correct grain.
- Use window functions to compare groups, rank them, calculate rolling statistics, or create relative measures.
- Feed the resulting table into a dashboard, statistical analysis, or machine-learning workflow.
Common mistakes to avoid
One common mistake is mixing grouped and ungrouped columns incorrectly. This query is invalid in most SQL engines:
SELECT
customer_id,
order_date,
SUM(revenue)
FROM orders
GROUP BY customer_id;
The problem is that order_date is neither grouped nor aggregated. SQL does not know which order date to choose for each customer.
Another common mistake is using an aggregate when a window is needed. If you need to see every transaction plus a customer average, use:
AVG(revenue) OVER (
PARTITION BY customer_id
)
rather than grouping by customer and losing the transaction records.
A third mistake is filtering window-function results in WHERE. Window functions are calculated after the WHERE clause in the logical query-processing order. For example, this usually will not work:
SELECT
customer_id,
revenue,
RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM orders
WHERE revenue_rank <= 10;
Instead, place the window calculation in a subquery or common table expression:
WITH ranked_orders AS (
SELECT
customer_id,
revenue,
RANK() OVER (
ORDER BY revenue DESC
) AS revenue_rank
FROM orders
)
SELECT *
FROM ranked_orders
WHERE revenue_rank <= 10;
Some databases provide a QUALIFY clause that makes this pattern more concise, but it is not universally available.
Finally, be cautious about unintended partitions. Leaving out PARTITION BY means the calculation runs across all rows. Including the wrong partition can create misleading results that appear plausible. Always ask: “Which rows should count as this row’s peers?”
Conclusion(Choosing the right tool)
Use aggregate functions when you want to reduce a dataset into a summary at a new grain. They are excellent for dashboard metrics, grouped reports, and compact analytical tables.
Use window functions when you need row-level detail plus group-level or time-based context. They are particularly valuable for ranks, percent-of-total calculations, running totals, rolling averages, prior-period comparisons, sessionization, and feature engineering.
Aggregate functions tell you what happened at a summary level. Window functions help explain how each observation fits into the broader pattern. Together, they turn SQL from a reporting language into a flexible system for analytical reasoning.
Top comments (0)