Aggregation Patterns for Business Metrics in SQL

Every business metric a stakeholder asks for boils down to one of a handful of SQL patterns. KPIs, breakdowns, ratios, rankings, running totals, moving averages, growth rates. Seven shapes, not seventy. Once you can recognize which shape a question is asking for, writing the query becomes a lookup instead of a guessing game.

That gap widens the divide between listing SQL as a skill and being able to apply it on the job. SQL is one of the most commonly listed skills in business analyst job postings, and LinkedIn's 2024 Jobs Report found more than 65% of listings naming it as required or preferred. But knowing SELECT and GROUP BY doesn't mean you know how to answer "what's our 30-day moving average?" on the spot. Plenty of analysts who write SQL every day still freeze up when someone asks for period-over-period growth. Why does that happen?

Because fluency in the language and fluency in the patterns are two different skills. You can know every clause in the SQL vocabulary and still not know which ones to combine when a VP asks for "the trend, but smoothed out." Worse, two analysts can take the exact same business question, both write technically correct SQL, and land on two different numbers. Not because one of them made an error, but because they picked different aggregation logic without realizing it was a choice.

LearnSQL's 2026 taxonomy, published in January 2026, sorts these questions into two buckets: standard metrics (KPIs, breakdowns, ratios, ranks) and specialty metrics (cumulative totals, moving averages, percentage change). The standard four appear constantly in questions, and the specialty three are where people get stuck. This is a guide to recognizing which pattern a question is really asking for, not a syntax reference. The goal is a repeatable way of thinking, not a list of queries to memorize.

KPI queries: returning a single authoritative number

A KPI answers one question: how are we doing overall? Total sales. Total profit. Total record count. One number, computed across the whole dataset, no slicing.

The pattern signature is simple: a single row, no GROUP BY, built on SUM, COUNT, or AVG.

SELECT 
  ROUND(SUM(sales), 0) AS sales_total, 
  ROUND(SUM(profit), 0) AS profit_total, 
  ROUND(SUM(quantity), 0) AS quantity_total 
FROM orders;

Notice that one table scan produces three metrics at once, just by stacking aggregate functions in the SELECT list. No need to run three separate queries.

LearnSQL's breakdown notes that KPI questions are often the first or simplest ones asked in a SQL interview. They're a checkpoint. Interviewers want to see whether the candidate picks the right level of aggregation without overcomplicating it.

The most common mistake here is adding a GROUP BY when none was asked for. Slip a GROUP BY into a KPI query and you no longer have a KPI. You have a breakdown, with multiple rows, and the "single number" the stakeholder wanted is now scattered across a table they didn't ask for.

Rounding the number and aliasing the column is one more thing worth doing. It sounds cosmetic. It sounds cosmetic, but it isn't. A well-labeled, rounded KPI is something you can drop straight into a dashboard or a report. An unlabeled raw float is something someone else has to clean up before they can use it.

Breakdown queries: splitting a KPI by time or category

A breakdown takes the exact same aggregate functions as a KPI and adds one thing: GROUP BY. That single addition changes the question from "what's the total?" to "what's driving the total?"

There are two flavors, per LearnSQL's taxonomy:

Trend by date: group by year or month. "Show total sales by month." "How has revenue changed over time?" Category breakdown: group by product, region, or segment. "Which region generates the most revenue?"

A standard shape, per SQLNoir's guide:

SELECT 
  region, 
  COUNT(*) AS total_orders, 
  SUM(order_amount) AS total_revenue, 
  AVG(order_amount) AS avg_order_value 
FROM orders 
GROUP BY region 
ORDER BY total_revenue DESC;

Three metrics, one table, one GROUP BY. This is the bread-and-butter query for top-performer reporting: join order items to products, group by product, sort by revenue descending, done.

One distinction trips people up constantly: WHERE filters rows before aggregation, HAVING filters after. If someone asks "which regions had more than 100 orders," that's a HAVING clause, because "more than 100 orders" is a statement about the aggregate, not about any single row. Get this backwards and the query either errors out or, worse, runs fine and returns the wrong answer.

Add ORDER BY on the aggregate column, descending, and a plain breakdown becomes a ranked report. That's the natural bridge into the next pattern.

Conditional aggregation: computing multiple segmented metrics in one query pass

Conditional aggregation solves the problem of someone wanting revenue broken out by active users, churned users, and trial users. The naive approach is three separate queries, each with its own WHERE clause. That's three table scans for one dashboard. Multiply that across a real reporting suite and the maintenance burden adds up fast.

The fix is a CASE WHEN nested inside SUM() or COUNT(), all inside a single GROUP BY:

SELECT 
  month, 
  SUM(CASE WHEN status = 'active' THEN revenue ELSE 0 END) AS active_revenue, 
  SUM(CASE WHEN status = 'churned' THEN revenue ELSE 0 END) AS churned_revenue 
FROM subscriptions 
GROUP BY month;

One pass over the table, multiple segmented columns out the other end. It answers "what share of revenue came from each plan tier this month?" in a single result set, which maps directly onto a dashboard widget without any post-processing.

This pattern is also becoming a target for automated tooling. This pattern isn't just a manual best practice, its structure is regular enough that it has become a target for automated tooling designed to reduce redundant query logic.

Ratio metrics: turning raw totals into performance rates

A KPI tells you the total. A ratio tells you whether that total is good. A ratio measures the relationship between two numbers by dividing one by the other, and LearnSQL's notes describe it as the standard way SQL interviews test whether a candidate can move past raw totals into relative performance.

Typical questions: What's the profit margin? What percentage of sales does each category represent? How much of X comes from Y?

The pattern is a derived column, SUM(a) divided by SUM(b), either inside a GROUP BY or as a window function against a grand total:

SELECT 
  category, 
  SUM(profit) / NULLIF(SUM(revenue), 0) AS margin 
FROM orders 
GROUP BY category;

Two failure modes to watch for. First, integer division. Some databases default to integer arithmetic, so 3 divided by 4 comes back as 0, not the fractional result you would expect. Cast an operand to DECIMAL or FLOAT before dividing, or the result truncates silently and nobody notices until the number looks suspiciously wrong.

Second, division by zero. If a category had zero revenue in a given period, dividing by that zero crashes the query. Wrapping the denominator in NULLIF(denominator, 0) turns that crash into a clean NULL instead. It's a small habit that saves a report from breaking at the worst possible moment, like the morning it's due.

Rank queries: ordering results by business importance

Rank metrics order results by importance: top customers by revenue, top products by volume, bottom performers by churn. Rank queries rely on window functions.

RANK() OVER (ORDER BY revenue DESC)

or, split by category:

DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC)

Two common shapes appear constantly in these queries. Simple top-N: wrap the ranked output in a CTE or subquery, then filter WHERE rank_col <= N. Top-N per category: add PARTITION BY inside the window, which answers something like "top 3 products per region" without needing a separate query per region.

RANK and DENSE_RANK behave differently on ties, and that difference matters. RANK skips numbers after a tie (1, 2, 2, 4). DENSE_RANK doesn't (1, 2, 2, 3). Which one is correct depends entirely on whether ties should count the next product as third or fourth, not on preference. If two products tie for second place, does the next one count as third or fourth? That's not a SQL question, it's a business rule the SQL has to reflect.

Rank queries matter beyond reporting, too. Cohort analysis and leaderboards often treat the rank itself as the metric, not the number behind it. Nobody cares that a customer generated a specific dollar figure in revenue. They care that the customer is #1.

RANK() is also the simplest window function to learn, and understanding it makes the next two patterns, cumulative totals and moving averages, much easier to read, since they reuse the same OVER() syntax with an added frame clause.

Cumulative metrics: running totals over time

A cumulative metric adds up a value from the first row to the current row, in order, usually by date. It accumulates from the beginning of the series up to that point.

This pattern appears across real business reporting in domains such as:

• Finance: year-to-date revenue as of any cut-off date, cumulative spend against budget
• Operations: cumulative orders shipped, tickets resolved, bugs closed
• Product: cumulative installs, cumulative cancellations

The pattern signature:

SUM(metric) OVER (ORDER BY date_column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Add PARTITION BY to reset the running total per entity:

SUM(metric) OVER (PARTITION BY account_id ORDER BY date)

That gives a running total per account instead of one running total across everyone.

Why can't GROUP BY do this? GROUP BY collapses rows into one row per group. A running total needs the opposite: one row per date, but with access to every row that came before it. Only a window function keeps both things true at once, which is exactly what "year-to-date as of any date" requires. No self-join, no correlated subquery, just a frame clause.

This frame clause is also where queries can quietly break. Leaving off the frame entirely may produce correct results on databases that default to the expected behavior, but the query isn't portable, and relying on that default is a bet that may not pay off the next time the query runs somewhere else.

Moving averages: smoothing trends to surface signal over noise

A moving average takes a metric and averages it over a rolling window, the current period plus some number of periods before it. The point isn't to compute a new number for its own sake. It's to make a noisy chart readable.

Common uses: a 7-day moving average of daily active users, a 30-day moving average of costs or conversions, a rolling window average over any recurring reporting period.

AVG(metric) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

That's the 7-day version. Swap the 6 for whatever window matches the reporting period needed, 29 for a 30-day average, and so on.

The frame clause is doing all the work here, and it's the same mechanism as the cumulative pattern above, just with a different boundary. ROWS BETWEEN N PRECEDING AND CURRENT ROW gives a rolling window. ROWS UNBOUNDED PRECEDING gives the cumulative total instead. Same syntax, different frame, completely different business meaning.

At the start of a time series, there aren't N prior rows yet. The average for day 3 of a 7-day rolling window is only averaging 3 days, not 7. That's not wrong, but it can distort the early part of a trend line if nobody mentions it. Worth a footnote on the dashboard.

Why does any of this matter to a stakeholder who doesn't write SQL? Because a raw daily metric on a busy product is often just noise. Spikes and dips that mean nothing. A 7-day moving average smooths that out and makes the actual trend visible. The SQL pattern isn't just a technical exercise, it changes what decision gets made from the chart.

Percentage change: measuring growth and decline between periods

Percentage change answers how much a metric grew or shrank between two periods: week-over-week, month-over-month, year-over-year. Revenue growth, user growth, conversion rate change, churn delta. All the same underlying shape.

The pattern uses LAG() to pull the prior period's value into the current row, then computes the difference as a percentage:

SELECT 
  month, 
  revenue, 
  LAG(revenue) OVER (ORDER BY month) AS prior_revenue, 
  (revenue - LAG(revenue) OVER (ORDER BY month)) 
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100 AS pct_change 
FROM monthly_revenue;

That query works, but notice it computes LAG(revenue) three separate times. A cleaner version computes it once in an inner query or CTE, then references it twice in the outer query. Same result, less redundant computation.

For growth by entity, whether that's by product, by cohort, or by region, add PARTITION BY:

LAG(revenue) OVER (PARTITION BY product_id ORDER BY month)

That gets growth rate per product without needing a self-join.

Percentage change rarely stands alone. It often combines with conditional aggregation (compute each period's total with a CASE WHEN) and breakdown (GROUP BY category) underneath it. It's built out of the earlier patterns, not a separate technique bolted on top.

Comparing periods that aren't actually equivalent is a mistake. February has 28 days. March has 31. Comparing raw totals month-over-month without normalizing for that difference can make March look like a growth story when it's really just a longer month. Before writing the LAG(), it's worth asking what "period" actually means for the metric in question.

When query performance becomes a metric reliability problem

Getting the pattern right doesn't help much if the query takes so long to run that nobody trusts the number by the time it finishes. Augment Code's 2025 SQL Total Queries Cheat Sheet found that nightly revenue roll-ups that crawl past their SLA windows leave finance teams sitting there, waiting on a report that's supposed to already be on their desk.

That cheat sheet points to two root causes behind enterprise-scale aggregate failures. First, performance degradation at scale: even a plain SUM() can crawl to a stop on a large table if there's no proper execution plan or index behind it. The query is logically correct. It's just too slow to be useful by the time it returns.

Second, data fragmentation: revenue data spread across multiple tables, sources, or systems, so a single aggregate number requires pulling from places that don't line up cleanly.

Both point to the same underlying truth: correctness and speed aren't separate concerns in metric reporting. A KPI query that returns the right number three hours late isn't a reliable KPI. It's a number nobody can act on until it's already outdated. Knowing the pattern gets the logic right. Making that pattern run fast enough to matter is essential to the job, not optional.