Aggregation and Grouping Interview Questions for SQL
24 Aggregation and Grouping questions with answers for sql roles — 7 easy, 12 medium, 5 hard.
About Aggregation and Grouping
Summarise data with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING. Learn conditional aggregation with CASE, ROLLUP and GROUPING SETS for subtotals, string aggregation, medians and percent of total. You will also learn how NULLs and grain mistakes affect business metrics such as average order value.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
Aggregate functions compute a single value from many rows: COUNT, SUM, AVG, MIN and MAX are standard, and databases add others such as STRING_AGG, GROUP_CONCAT, STDDEV and MEDIAN or PERCENTILE_CONT. Used with GROUP BY they return one value per group; without it they return one value for the whole table. Except COUNT(*), they ignore NULL values.
02
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*) counts all rows including those with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. In a table of 100 orders where 10 have no coupon and 30 distinct coupons were used, COUNT(*) is 100, COUNT(coupon) is 90 and COUNT(DISTINCT coupon) is 30.
03
What is GROUP BY and what is the rule for selected columns?
GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.
AVG ignores NULLs, dividing the sum of non-null values by the count of non-null values. If three employees have bonuses 1000, 2000 and NULL, AVG(bonus) is 1500, not 1000. If NULL should mean zero, use AVG(COALESCE(bonus, 0)). Interviewers ask this to check whether you understand how missing data changes business metrics.
05
Find the number of employees and average salary in each department.
SELECT department_id,
COUNT(*) AS emp_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;
Join with the departments table if department names are needed, and use LEFT JOIN from departments to include empty departments.
SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY customers DESC;
HAVING is needed because the condition uses an aggregate. A WHERE clause could still filter rows first, for example only active customers.
07
What is the difference between MIN/MAX on numbers and on strings or dates?
MIN and MAX work on any comparable type. On numbers they return smallest and largest values, on dates the earliest and latest, and on strings the first and last in alphabetical order based on collation. MAX(order_date) gives the latest order, and MIN(name) gives the alphabetically first name, which is useful but not a meaningful business metric by itself.
In standard SQL, GROUP BY and HAVING are evaluated before SELECT, so aliases are not available there. MySQL and PostgreSQL allow aliases in GROUP BY, and MySQL allows them in HAVING, but SQL Server does not. For portable code, repeat the expression or wrap the query in a subquery or CTE and filter on the alias in the outer query.
They produce multiple grouping levels in one query. ROLLUP(region, city) gives city totals, region subtotals and a grand total. CUBE(region, product) gives all combinations of subtotals. GROUPING SETS lets you list exact combinations, such as (region), (product) and (). The GROUPING function identifies subtotal rows, which is useful for labelling them in reports.
10
What is the difference between SUM with CASE and filtering with WHERE?
WHERE removes rows from the entire query, so all aggregates see only filtered data. SUM(CASE WHEN condition THEN amount END) applies the condition to one aggregate only, letting you calculate several conditional totals in one pass, such as paid and refunded amounts per customer. Some databases support the cleaner FILTER clause: SUM(amount) FILTER (WHERE status = 'Paid').
Use string aggregation: STRING_AGG(name, ', ') in PostgreSQL and SQL Server, GROUP_CONCAT(name SEPARATOR ', ') in MySQL, and LISTAGG in some other databases. Most support ordering inside, for example STRING_AGG(name, ', ' ORDER BY name). It is used to list products in an order or skills of an employee in one row, but watch length limits.
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
To see full duplicate rows:
SELECT * FROM users
WHERE email IN (
SELECT email FROM users
GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY email;
Normalise case with LOWER(email) if needed.
13
Write a query for monthly revenue for the current year.
SELECT EXTRACT(MONTH FROM order_date) AS month,
SUM(amount) AS revenue,
COUNT(DISTINCT customer_id) AS buyers
FROM orders
WHERE order_date >= DATE '2025-01-01'
AND order_date < DATE '2026-01-01'
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;
A date range in WHERE keeps an index on order_date usable.
14
Produce region subtotals and a grand total in one query.
SELECT COALESCE(region, 'All Regions') AS region,
COALESCE(city, 'Subtotal') AS city,
SUM(sales) AS sales
FROM store_sales
GROUP BY ROLLUP(region, city)
ORDER BY region, city;
In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.
15
Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?
Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.
16
Average order value looks too low in a report. What could be wrong?
Check the grain: if the query averages order items instead of orders, each line counts separately and the average drops. Joins can also duplicate rows. Verify whether cancelled or zero-value orders are included and how NULL amounts are treated. Compute SUM(amount) / COUNT(DISTINCT order_id) at order level and compare with business definitions before changing the report.
17
Management wants the percentage of each category in total sales. How do you write it?
Use a window aggregate over the grouped result: SELECT category, SUM(sales) AS sales, ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 2) AS pct FROM sales GROUP BY category. The inner SUM is per category and the outer SUM over an empty window gives the grand total. Multiplying by 100.0 avoids integer division in some databases.
18
Tell me about a time you had to deliver a report under a tight deadline.
Situation: the sales head needed region-wise quarterly numbers for a client meeting the same afternoon. Task: deliver accurate figures within three hours. Action: I confirmed metric definitions, reused tested queries, wrote one grouped query with ROLLUP for subtotals and cross-checked totals with the finance summary. Result: the report was delivered in two hours, and it later became a scheduled weekly report.
19
A COUNT(DISTINCT) query on a huge events table is very slow. What options do you have?
Exact distinct counts need sorting or hashing all values, which is expensive. Options are filtering the date range tightly, pre-aggregating daily distinct users into a summary table, indexing the columns involved, or using approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog where small error is acceptable. Explain the accuracy trade-off to stakeholders before switching to approximations.
The optimiser uses either hash aggregation, building a hash table keyed by group values, which is fast for unsorted data with enough memory, or stream (sort) aggregation, which needs input sorted by the group keys, often from an index. Large groups that do not fit memory spill to disk. Indexes on grouping columns and pre-filtering reduce the cost.
PostgreSQL and standard SQL:
SELECT department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
AS median_salary
FROM employees
GROUP BY department_id;
SQL Server uses PERCENTILE_CONT as a window function with OVER (PARTITION BY department_id). Median resists outliers better than average for salary analysis.
22
A daily aggregation over 2 billion rows is too slow for a dashboard. What would you propose?
Avoid recalculating raw data on every request. Build a summary table or materialised view at daily grain refreshed incrementally, partition the fact table by date, and use columnar storage if available. Pre-compute common metrics, index the summary on dimensions used in filters, and let the dashboard query the summary. Validate summary totals against raw data periodically.
23
How do you handle a situation where business users dispute your numbers?
Situation: a regional manager said my sales report undercounted his region. Task: resolve it with facts. Action: I shared the query logic and definitions, compared a sample of his records with the database, and found his team counted orders on booking date while the report used invoice date. Result: we agreed on invoice date with finance, documented it, and added the definition to the report.
24
Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?
Aggregate in two levels. First compute total spend per customer and city in a CTE: SELECT city, customer_id, SUM(amount) AS spend FROM orders GROUP BY city, customer_id. Then in the outer query: SELECT city, COUNT(*) FROM cte WHERE spend > 100000 GROUP BY city. Trying to do it in one GROUP BY by city fails because the threshold applies to each customer, not each city.
Frequently asked questions
How important is Aggregation and Grouping in SQL interviews?
Summarise data with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING. Learn conditional aggregation with CASE, ROLLUP and GROUPING SETS for subtotals, string aggregation, medians and percent of total. You will also learn how NULLs and grain mistakes affect business metrics such as average order value.
How should I use the Aggregation and Grouping sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.