Context

Multi-level aggregations come up often in analytics work: for example, calculating daily totals first and then rolling those up into monthly or regional summaries. Interviewers ask this to test whether you understand how SQL processes grouped data and how to structure queries cleanly.

Core Question

Explain how you would handle multi-level aggregations in SQL. Your answer should cover:

1. Why a single GROUP BY is sometimes not enough
2. How to use a subquery or CTE to aggregate in stages
3. When window functions help versus when they do not replace grouped aggregation
4. Common mistakes, such as aggregating at the wrong grain or double-counting after joins

Scope Guidance

The interviewer expects a practical conceptual explanation, not just a definition. You should describe the idea of aggregating at one level first, then using that result set for a second aggregation, and mention how to choose the correct grain before writing the query.

Aggregation Grain

Before writing the query, define the level of detail you need at each stage, such as order-level, customer-level, or month-level. Most multi-level aggregation errors happen because the query mixes grains and sums data after duplication has already occurred.

SELECT customer_id, order_date, SUM(amount) AS daily_revenue
FROM orders
GROUP BY customer_id, order_date;

Stage Aggregations with CTEs or Subqueries

A common pattern is to aggregate once in an inner query, then aggregate again in an outer query. This makes the logic explicit and avoids trying to nest aggregate functions directly in a single SELECT.

WITH daily_sales AS (
  SELECT store_id, sale_date, SUM(amount) AS daily_total
  FROM sales
  GROUP BY store_id, sale_date
)
SELECT store_id, AVG(daily_total) AS avg_daily_sales
FROM daily_sales
GROUP BY store_id;

Window Functions vs Grouped Aggregation

Window functions calculate values across related rows without collapsing them into one row per group. They are useful after an aggregation step, but they do not replace the need to group when you need fewer rows at a higher summary level.

WITH monthly_sales AS (
  SELECT store_id, DATE_TRUNC('month', sale_date) AS month_start, SUM(amount) AS monthly_total
  FROM sales
  GROUP BY store_id, DATE_TRUNC('month', sale_date)
)
SELECT store_id, month_start, monthly_total,
       RANK() OVER (PARTITION BY month_start ORDER BY monthly_total DESC) AS sales_rank
FROM monthly_sales;

Join Duplication Risk

If you join fact tables to detail tables before aggregating, you may multiply rows and inflate totals. In many cases, the correct approach is to aggregate first at the needed grain, then join to dimensions or perform later rollups.

WITH order_totals AS (
  SELECT o.customer_id, o.order_id, SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY o.customer_id, o.order_id
)
SELECT customer_id, AVG(order_total) AS avg_order_value
FROM order_totals
GROUP BY customer_id;

HAVING for Post-Aggregation Filtering

When filtering based on aggregate results, use HAVING after the relevant grouping step. In multi-level aggregation, HAVING may appear in the inner query, outer query, or both depending on which level the condition applies to.

WITH customer_monthly AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS month_start, SUM(amount) AS monthly_spend
  FROM orders
  GROUP BY customer_id, DATE_TRUNC('month', order_date)
  HAVING SUM(amount) > 100
)
SELECT month_start, COUNT(*) AS active_customers
FROM customer_monthly
GROUP BY month_start;