Nested Aggregations in SQL
Introduction
Nested aggregation means summarizing data at more than one logical level. A common requirement is to calculate sales for each day and then calculate the average of those daily totals. The first aggregation converts order rows into daily rows; the second treats those daily summaries as its input.
WITH daily_sales AS (
SELECT CAST(order_date AS DATE) AS sales_date,
SUM(total_amount) AS daily_total
FROM orders
GROUP BY CAST(order_date AS DATE)
)
SELECT AVG(daily_total) AS average_daily_sales
FROM daily_sales;
Nested aggregation is primarily about changing grain in controlled stages. It is not simply placing one aggregate function inside another. In most ordinary query blocks, direct forms such as AVG(SUM(total_amount)) are invalid or do not express the intended levels. A subquery, derived table, common table expression, or window stage creates the intermediate rows needed by the next calculation.
What Is Aggregation?
Aggregation combines multiple input rows into fewer summary rows. If customer 101 has orders of 100 and 200, and customer 102 has orders of 300 and 500, grouping by customer and summing produces two rows: 300 for customer 101 and 800 for customer 102.
SELECT customer_id,
SUM(amount) AS customer_total
FROM orders
GROUP BY customer_id;
The original grain was one row per order. The new grain is one row per customer. Every aggregation should be described with this “one row means” statement.
What Makes Aggregation Nested?
If the next question is average spending per customer, the customer totals become a second dataset. Averaging 300 and 800 gives 550. This differs from calculating average order amount, which averages the four raw orders and gives 275.
SELECT AVG(customer_total) AS average_customer_spending
FROM (
SELECT customer_id,
SUM(amount) AS customer_total
FROM orders
GROUP BY customer_id
) AS customer_totals;
The inner query produces one row per customer. The outer query produces one overall row. That explicit separation is the essence of nested aggregation.
Why Nested Aggregations Matter
Many business metrics naturally cross grains: average daily revenue, maximum monthly sales, average orders per customer, number of high-selling products, largest department size, average defects per project, and average daily API failures. A single aggregate over raw rows answers a different question because raw observations do not have equal group sizes.
Nested stages also help teams inspect intermediate results, reuse summaries, apply thresholds at the correct level, and prevent joins from corrupting metrics.
Query Grain Is the Key Concept
| Stage | One row represents | Example metric |
|---|---|---|
| Raw | One order | Order amount |
| Level 1 | One day | Daily sales |
| Level 2 | One month | Average daily sales in month |
| Level 3 | One year | Maximum monthly average |
Before adding another aggregate, state the input grain and desired output grain. If those statements are unclear, the SQL is not ready to be written.
Why Direct Aggregate Nesting Is Usually Invalid
-- Usually invalid in one ordinary query block
SELECT AVG(SUM(total_amount))
FROM orders;
SUM requires a grouping context and produces summary rows. AVG must then consume those produced rows. One query block does not automatically create the required intermediate relation. Separate query levels make the two operations well-defined.
Derived Table Solution
SELECT AVG(daily_total) AS average_daily_sales
FROM (
SELECT CAST(order_date AS DATE) AS sales_date,
SUM(total_amount) AS daily_total
FROM orders
GROUP BY CAST(order_date AS DATE)
) AS daily;
A derived table is concise when the first summary is used once. Its alias is required in many database systems and gives the intermediate result a meaningful name.
CTE Solution
A common table expression names the first aggregation before the outer query. It is often easier to read when several stages, joins, or filters are involved.
WITH daily AS (
SELECT CAST(order_date AS DATE) AS sales_date,
SUM(total_amount) AS daily_total
FROM orders
GROUP BY CAST(order_date AS DATE)
)
SELECT AVG(daily_total) AS average_daily_sales,
MIN(daily_total) AS minimum_daily_sales,
MAX(daily_total) AS maximum_daily_sales
FROM daily;
A CTE does not inherently guarantee materialization or better performance. It primarily defines structure; the optimizer decides how to execute it.
Average Order Value vs Average Daily Revenue
AVG(total_amount) over orders calculates average order value. AVG(daily_total) over daily summaries calculates average revenue per represented day. A day with one order and a day with one hundred orders each contribute one daily value to the second average.
Neither metric is universally better. They answer different questions, and their labels must name the grain.
Maximum and Minimum Daily Sales
WITH daily_sales AS (
SELECT CAST(order_date AS DATE) AS sales_date,
SUM(total_amount) AS daily_total
FROM orders
WHERE status = 'Completed'
GROUP BY CAST(order_date AS DATE)
)
SELECT MIN(daily_total) AS lowest_daily_sales,
MAX(daily_total) AS highest_daily_sales
FROM daily_sales;
MIN of raw order amount would find the smallest order, not the lowest daily total. MAX of raw amount would find the largest order, not the highest-sales day.
Returning the Day Associated with an Extreme
An outer MAX returns only the extreme value. To retrieve the day, rank daily rows or compare them with the maximum.
WITH daily_sales AS (
SELECT CAST(order_date AS DATE) AS sales_date,
SUM(total_amount) AS daily_total
FROM orders
GROUP BY CAST(order_date AS DATE)
),
ranked AS (
SELECT daily_sales.*,
DENSE_RANK() OVER (ORDER BY daily_total DESC) AS sales_rank
FROM daily_sales
)
SELECT sales_date, daily_total
FROM ranked
WHERE sales_rank = 1;
DENSE_RANK returns every tied day. ROW_NUMBER would force one row and needs a deterministic secondary ordering rule.
Monthly Revenue
Monthly summaries should normally group by both year and month. Grouping only by month number combines January across all years.
WITH monthly_sales AS (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
SUM(total_amount) AS monthly_total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
)
SELECT AVG(monthly_total) AS average_monthly_revenue,
MAX(monthly_total) AS maximum_monthly_revenue
FROM monthly_sales;
Date extraction syntax varies. The group key must also use the reporting timezone if source timestamps are stored in UTC.
Average Customer Spending
WITH customer_totals AS (
SELECT customer_id,
SUM(total_amount) AS customer_total
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
)
SELECT AVG(customer_total) AS average_customer_spending
FROM customer_totals;
This averages customers who have at least one completed order. It does not include registered customers with zero completed spending. Population is a separate decision from calculation.
Including Zero-Activity Entities
To include customers with zero orders, begin with the customer dimension and LEFT JOIN an order summary.
WITH customer_totals AS (
SELECT c.customer_id,
COALESCE(SUM(o.total_amount), 0) AS customer_total
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'Completed'
GROUP BY c.customer_id
)
SELECT AVG(customer_total) AS average_spending_all_customers
FROM customer_totals;
Placing the order status condition in the JOIN preserves customers without a completed order. Placing it in WHERE would remove the null-extended rows and turn the outer join into an effective inner join.
Average Orders per Customer
WITH customer_counts AS (
SELECT customer_id,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT AVG(1.0 * order_count) AS average_orders_per_customer
FROM customer_counts;
The decimal multiplication prevents integer truncation in systems where AVG of an integer expression returns limited precision. Again, this population contains ordering customers unless a customer table is included.
Largest and Smallest Department Size
WITH department_sizes AS (
SELECT department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
)
SELECT MIN(employee_count) AS smallest_department,
MAX(employee_count) AS largest_department
FROM department_sizes;
The outer values describe represented departments. Empty departments require a LEFT JOIN from the department table so they produce a zero count.
Counting Qualifying Groups
Nested aggregation commonly counts how many groups satisfy a first-level threshold.
WITH customer_counts AS (
SELECT customer_id,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10
)
SELECT COUNT(*) AS high_activity_customers
FROM customer_counts;
The first stage filters customer groups; the second counts the surviving group rows. COUNT over raw orders would answer a different question.
Filtering Before and After the First Aggregation
WHERE inside the first stage determines which raw rows contribute. HAVING inside that stage removes first-level groups. An outer WHERE filters the intermediate summary, and an outer HAVING can filter a second grouping level. Place each rule at the level where its value exists.
WITH monthly_sales AS (
SELECT region,
EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
SUM(total_amount) AS monthly_total
FROM orders
WHERE status = 'Completed'
GROUP BY region,
EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
HAVING COUNT(*) >= 10
)
SELECT region,
AVG(monthly_total) AS average_qualifying_month
FROM monthly_sales
GROUP BY region;
This averages only region-months with at least ten completed orders. It is not the average across all months.
Average of Averages Problem
A simple average of group averages gives every group equal weight. If one class has two students averaging 90 and another has one hundred students averaging 70, the unweighted average of class averages is 80, but the average student score is much closer to 70.
An average of averages is correct only when equal group weighting is the intended metric or group sizes are equal. Otherwise preserve sum and count.
Weighted Average Rollup
WITH class_stats AS (
SELECT class_id,
SUM(score) AS score_sum,
COUNT(score) AS score_count
FROM exam_results
GROUP BY class_id
)
SELECT SUM(score_sum) / NULLIF(SUM(score_count), 0)
AS overall_student_average
FROM class_stats;
This reconstructs the overall average from additive components. It also supports higher-level rollups without carrying every detail row.
When Nested SUM Is Redundant
Summing group sums often equals summing raw values when groups are disjoint, the measure is additive, and no groups are filtered. In that case, a nested SUM may add complexity without changing the result. It becomes meaningful when the intermediate stage applies filtering, deduplication, currency conversion, snapshot selection, or another transformation.
Nested COUNT Patterns
COUNT at the outer level counts intermediate group rows. It can answer how many products exceeded a sales threshold, how many days had failures, or how many departments are understaffed.
WITH product_sales AS (
SELECT product_id,
SUM(quantity) AS units_sold
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) > 1000
)
SELECT COUNT(*) AS high_selling_product_count
FROM product_sales;
Missing Days and Zero-Activity Days
A daily aggregate over orders produces rows only for dates that contain orders. Average daily sales over that result excludes zero-order days. If the reporting period contains seven calendar days but only five have orders, the computed average divides by five.
To include zero-activity days, generate or join a calendar table containing every date, LEFT JOIN daily sales, and replace missing totals with zero only because the business definition treats no orders as zero revenue.
Calendar Table Solution
WITH daily_sales AS (
SELECT CAST(order_date AS DATE) AS sales_date,
SUM(total_amount) AS daily_total
FROM orders
WHERE order_date >= :start_date
AND order_date < :end_date
GROUP BY CAST(order_date AS DATE)
),
complete_days AS (
SELECT c.calendar_date,
COALESCE(d.daily_total, 0) AS daily_total
FROM calendar c
LEFT JOIN daily_sales d
ON d.sales_date = c.calendar_date
WHERE c.calendar_date >= :start_date
AND c.calendar_date < :end_date
)
SELECT AVG(daily_total) AS average_per_calendar_day
FROM complete_days;
The calendar defines the denominator explicitly and supports weekdays, holidays, fiscal periods, and reporting flags.
NULL Behavior Across Levels
Aggregates generally ignore NULL inputs. An inner SUM over an all-NULL group returns NULL, and an outer AVG then ignores that group's NULL result. This may remove a group from the denominator without being obvious.
Decide whether missing means unknown, zero, inapplicable, or invalid. Apply COALESCE at the level where that business meaning is established, not merely to make the output numeric.
Conditional Nested Aggregation
Conditional aggregates can build several first-level metrics, which an outer query then summarizes.
WITH project_quality AS (
SELECT project_id,
SUM(CASE WHEN severity = 'Critical' THEN 1 ELSE 0 END)
AS critical_defects,
COUNT(*) AS total_defects
FROM defects
GROUP BY project_id
)
SELECT AVG(1.0 * critical_defects) AS average_critical_per_project,
MAX(critical_defects) AS maximum_critical_in_project
FROM project_quality;
Projects with zero defects are absent unless a projects table is left joined into the first stage.
Software Testing Example
Suppose the requirement is average failed tests per execution day. The inner query counts failures for each day; the outer query averages daily counts. Averaging a failure indicator over individual tests would instead calculate failure rate.
WITH daily_failures AS (
SELECT CAST(executed_at AS DATE) AS execution_date,
SUM(CASE WHEN result = 'Failed' THEN 1 ELSE 0 END)
AS failed_tests
FROM test_results
GROUP BY CAST(executed_at AS DATE)
)
SELECT AVG(1.0 * failed_tests) AS average_daily_failures
FROM daily_failures;
API Monitoring Example
Average daily request count gives every represented day equal weight, unlike average requests per hour or per endpoint.
WITH daily_requests AS (
SELECT CAST(observed_at AS DATE) AS observation_date,
COUNT(*) AS request_count
FROM api_requests
WHERE environment = 'Production'
GROUP BY CAST(observed_at AS DATE)
)
SELECT AVG(1.0 * request_count) AS average_daily_requests,
MAX(request_count) AS peak_daily_requests
FROM daily_requests;
Use a calendar when days with no observations should count as zero rather than disappear.
Three-Level Aggregation
Some questions need three stages. For example, calculate daily revenue, average those daily totals per month, and find the highest monthly daily average per year.
WITH daily AS (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
CAST(order_date AS DATE) AS sales_date,
SUM(total_amount) AS daily_total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date),
CAST(order_date AS DATE)
),
monthly AS (
SELECT sales_year,
sales_month,
AVG(daily_total) AS average_daily_total
FROM daily
GROUP BY sales_year, sales_month
)
SELECT sales_year,
MAX(average_daily_total) AS best_monthly_daily_average
FROM monthly
GROUP BY sales_year;
Read complex nested aggregation from the inside out, writing down what one row represents after each stage.
Nested Aggregation with JOINs
Joining details before aggregation can change grain. If orders are joined to items, one row represents an item, not an order. Average of quantity * unit_price becomes average item-line value, not average order value.
First create one order total, then aggregate order totals at the customer or overall level.
WITH order_totals AS (
SELECT o.order_id,
o.customer_id,
SUM(i.quantity * i.unit_price) AS order_total
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.order_id, o.customer_id
)
SELECT AVG(order_total) AS average_order_value
FROM order_totals;
Join Multiplication Risk
Joining orders to both items and payments can multiply rows when both relationships are one-to-many. Nested aggregation cannot correct corrupted first-stage data. Pre-aggregate each child table separately to one row per order before joining them.
DISTINCT is not a general repair. Two legitimate item lines may have equal amounts, and SUM(DISTINCT amount) would drop one.
Nested Aggregation with Window Functions
A window function can add a higher-level metric without collapsing intermediate rows. The following keeps every department summary while adding the average department payroll across departments.
WITH department_payroll AS (
SELECT department_id,
SUM(salary) AS payroll
FROM employees
GROUP BY department_id
)
SELECT department_id,
payroll,
AVG(payroll) OVER () AS average_department_payroll,
payroll - AVG(payroll) OVER () AS difference_from_average
FROM department_payroll;
An outer aggregate would collapse the department rows. The window preserves them and supplies comparison context.
Filtering Window Results
Window results generally require an outer query for filtering because WHERE at the same level runs before windows.
WITH department_payroll AS (
SELECT department_id,
SUM(salary) AS payroll
FROM employees
GROUP BY department_id
),
benchmarked AS (
SELECT department_payroll.*,
AVG(payroll) OVER () AS average_payroll
FROM department_payroll
)
SELECT *
FROM benchmarked
WHERE payroll > average_payroll;
Every Aggregation Loses Detail
Once order rows become daily totals, individual order IDs are no longer available unless separately preserved. Do not select unrelated detail columns beside a grouped aggregate. If the final result needs the source row associated with an extreme, rank detail rows or join the summary back using a documented tie policy.
Precision and Data Types
Nested calculations can magnify rounding. Avoid rounding first-stage values unless the business rule requires it. Use exact decimal types for financial measures, cast integer divisions deliberately, and define the scale of averages and ratios. Currency conversion should occur at an approved stage using the correct effective rate.
Time Zones and Calendar Boundaries
Daily and monthly grouping must use the reporting timezone. Convert UTC timestamps before deriving local dates. Use half-open raw timestamp ranges for period filtering, and test midnight, month-end, leap-day, and daylight-saving transitions.
Maximum Monthly Revenue per Year
A common per-parent question is to find each year's highest monthly revenue. The inner query creates one row per year-month, and the outer query groups those monthly rows by year.
WITH monthly_sales AS (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
SUM(total_amount) AS monthly_total
FROM orders
WHERE status = 'Completed'
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
)
SELECT sales_year,
MAX(monthly_total) AS highest_monthly_revenue
FROM monthly_sales
GROUP BY sales_year
ORDER BY sales_year;
This returns the value but not necessarily the month. To return every tied month, rank the monthly rows within each year by monthly total and retain rank one. The tie policy belongs to the requirement, not to MAX itself.
Average Daily Orders per Month
The same structure can calculate a monthly average of daily counts. First count orders per calendar day, then average those counts for each month.
WITH daily_counts AS (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
CAST(order_date AS DATE) AS sales_date,
COUNT(*) AS daily_orders
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date),
CAST(order_date AS DATE)
)
SELECT sales_year,
sales_month,
AVG(1.0 * daily_orders) AS average_daily_orders
FROM daily_counts
GROUP BY sales_year, sales_month;
Without a calendar join, days with zero orders are absent and do not lower the average. The metric should be labeled “per active day” if that exclusion is intentional.
Filtering Intermediate Groups
A threshold applied in the first stage changes the population available to the second. Suppose a report asks for average revenue among high-volume months, where a high-volume month has at least 1,000 orders. HAVING belongs in the monthly stage before AVG is calculated.
WITH high_volume_months AS (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
COUNT(*) AS order_count,
SUM(total_amount) AS monthly_total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
HAVING COUNT(*) >= 1000
)
SELECT AVG(monthly_total) AS average_high_volume_month_revenue
FROM high_volume_months;
Applying an outer filter after averaging would answer a different question. Every condition should be placed where its required value and intended grain exist.
Deduplicate Before the First Aggregate
Event and transaction tables sometimes retain revisions. Aggregating every physical version can double-count facts. The first logical stage may need to select the authoritative revision before any business aggregation.
WITH ranked_revisions AS (
SELECT e.*,
ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY revision_number DESC, loaded_at DESC
) AS revision_rank
FROM event_history e
),
daily_events AS (
SELECT CAST(event_time AS DATE) AS event_date,
COUNT(*) AS event_count
FROM ranked_revisions
WHERE revision_rank = 1
AND is_deleted = 0
GROUP BY CAST(event_time AS DATE)
)
SELECT AVG(1.0 * event_count) AS average_daily_events
FROM daily_events;
This is a staged transformation followed by nested aggregation. Using DISTINCT as a shortcut would not reliably select the latest revision or honor deletions.
Compare Each Group with a Higher-Level Benchmark
Nested summaries often produce a benchmark against which intermediate groups are compared. For example, a regional sales total can be compared with the average regional total.
WITH regional_sales AS (
SELECT region,
SUM(amount) AS regional_total
FROM sales
GROUP BY region
),
benchmark AS (
SELECT AVG(regional_total) AS average_region_total
FROM regional_sales
)
SELECT r.region,
r.regional_total,
b.average_region_total
FROM regional_sales r
CROSS JOIN benchmark b
WHERE r.regional_total > b.average_region_total;
A window average over regional_sales can produce the same comparison with fewer named stages. The CTE form is useful when the benchmark is reused or combined with additional overall metrics.
Nested Percentages and Denominator Grain
Percentages require special care because the denominator may belong to a different grain. To calculate each product's percentage of category sales, first create product totals, then calculate category totals from those product rows or use a window over the product summary.
WITH product_sales AS (
SELECT category_id,
product_id,
SUM(amount) AS product_total
FROM sales
GROUP BY category_id, product_id
)
SELECT category_id,
product_id,
product_total,
100.0 * product_total
/ NULLIF(SUM(product_total) OVER (PARTITION BY category_id), 0)
AS category_percentage
FROM product_sales;
The window denominator sums already-correct product totals within each category. If source rows were multiplied before product aggregation, the percentage would still be wrong.
Reusable Intermediate Summaries
A well-designed intermediate summary can serve several outer calculations. Daily sales may feed average daily revenue, peak-day detection, week-over-week reporting, and anomaly monitoring. Name its grain and measures clearly, preserve useful additive components, and avoid embedding one report's display formatting in the reusable layer.
When a summary becomes a persistent view or table, document freshness, source cutoff, timezone, currency, late-data policy, and reconciliation process. The physical object should have the same semantic contract as the CTE it replaces.
Audit Intermediate Results
Complex nested queries are safest when each CTE can be executed and checked independently. Compare first-stage row counts with expected distinct groups, reconcile additive totals with raw data, and inspect groups at boundaries. Add temporary diagnostic columns such as source count, non-NULL count, minimum timestamp, and maximum timestamp.
This practice distinguishes an error in source preparation from an error in the outer rollup. It also gives reviewers evidence that every grain transition is intentional.
Performance Considerations
Each aggregation stage can require scans, joins, sorts or hashes, memory, temporary storage, and distributed data movement. Filter raw rows early when doing so preserves semantics, project only required columns, and avoid grouping at unnecessary detail.
Indexes on selective filters and join keys may reduce first-stage input. Later stages usually process far fewer summary rows. Inspect the full execution plan because a readable multi-CTE query may be inlined, materialized, or optimized differently by each database.
Pre-Aggregation in Large Systems
Frequently reused daily or customer summaries can be stored in materialized views or aggregate tables. These reduce repeated detail scans but introduce refresh schedules, late-arriving data, corrections, deletions, and reconciliation requirements. Preserve additive components such as sum and count when later weighted rollups are needed.
Snapshot and Non-Additive Measures
Balances, inventory on hand, active users, and storage usage may not be additive across time. Summing daily balances to obtain a monthly balance is usually meaningless. The first stage may need latest value per day, and the second may need average daily balance or month-end balance. Metric behavior must be defined before nesting aggregates.
Transaction Consistency
Concurrent updates can make separately executed stages or related report queries observe different moments. A single SQL statement commonly uses one statement snapshot under appropriate isolation, but database behavior varies. Reconciled reports should use a consistent snapshot or explicit cutoff and record the as-of time.
Testing Nested Aggregations
Validate each intermediate result independently. Use datasets with unequal group sizes, missing groups, zero-activity entities, NULL measures, duplicate values, ties, negative values, and threshold boundaries. Include days without facts when calendar behavior matters.
For joins, create one-to-many and many-to-many cases that expose multiplication. For weighted averages, compare the staged result with a direct calculation over detail rows. For three-level queries, document expected rows at every grain.
Common Mistakes
- Writing direct aggregate nesting without creating an intermediate query level.
- Using the wrong inner grain.
- Confusing average order value with average daily revenue.
- Grouping by month number without year.
- Ignoring missing dates or zero-activity entities.
- Averaging group averages without weighting.
- Using DISTINCT to hide join multiplication.
- Losing tied groups when retrieving an associated maximum.
- Filtering at the wrong query level.
- Mixing timezones, currencies, or units.
- Rounding intermediate values too early.
- Nesting aggregates when one direct additive aggregate is equivalent.
Nested Aggregation Design Method
- Write the business question in plain language.
- Define the raw row grain.
- Define what one first-stage row must represent.
- Choose first-stage dimensions, filters, and aggregates.
- Validate the first-stage output independently.
- Define the second-stage grain and calculation.
- Decide how missing groups, zero, and NULL behave.
- Add more stages only when another grain change is required.
- Verify joins, weights, boundaries, and ties.
- Inspect performance with representative data.
Interview Questions and Answers
What is nested aggregation?
It is aggregation across multiple logical query levels, where one stage produces summary rows consumed by another stage.
Why is AVG(SUM(amount)) usually invalid?
SUM and AVG require separate aggregation contexts. Create SUM results in a subquery or CTE and apply AVG in the outer query.
What is the main design concept?
Grain. State what one row represents before and after every aggregation stage.
How do you include customers with zero orders?
Start from customers, LEFT JOIN orders, calculate zero-aware customer totals, and then aggregate those customer rows.
Why is an average of averages dangerous?
It gives equal weight to groups that may contain different numbers of observations. Preserve sum and count for a weighted rollup.
Can window functions help?
Yes. After a grouped stage, windows can add higher-level metrics while preserving each intermediate group row.
How do you count groups above a threshold?
Filter groups with HAVING in the inner query, then count the surviving rows in the outer query.
Complete Mental Model
Raw detail rows
|
v
Aggregation level 1
one row per first business grain
|
v
Aggregation level 2
one row per higher business grain
|
v
Optional window, ranking, or filter
|
v
Final result at the required grain
Read nested queries from the inside out. At every boundary, ask what one row now represents, which information was lost, and whether the next aggregate should weight those rows equally.
Key Takeaway
Nested aggregation is a controlled movement between data grains. The inner query creates the first summary dataset, and the outer query summarizes those rows at another level.
RELIABLE NESTED AGGREGATION = CORRECT INNER GRAIN
+ VALID POPULATION
+ SAFE JOINS
+ EXPLICIT MISSING-GROUP RULES
+ CORRECT WEIGHTING
+ DEFINED OUTER GRAIN
+ VERIFIED INTERMEDIATE RESULTS
Do not begin with nested function syntax. Begin by describing each intermediate table. Once every row has a clear meaning, the required CTEs, aggregates, filters, and windows follow naturally.