Aggregation Functions and Group By Questions

Fundamentals of aggregation in Structured Query Language covering aggregate functions such as COUNT, SUM, AVG, MIN, and MAX and how to use them to calculate totals, averages, minima, maxima, and row counts. Includes mastery of the GROUP BY clause to group rows by one or more dimensions such as customer, product, region, or time period, and producing metrics like total revenue by month, average order value by product, or count of transactions by date. Covers the HAVING clause for filtering aggregated groups and explains how it differs from WHERE, which filters rows before aggregation. Also addresses related topics commonly tested in interviews and practical problems: grouping by multiple columns, grouping on expressions and date truncation, using DISTINCT inside aggregates, handling NULL values, ordering and limiting grouped results, using aggregates in subqueries or derived tables, and basic performance considerations when aggregating large datasets. Practice examples include calculating monthly revenue, finding customers with more than a threshold number of orders, and identifying top products by sales.

MediumTechnical

0 practiced

Given orders(order_id, customer_id, amount, created_at), write a query to find customers who placed more than 10 orders and have an average order amount greater than 100. Return customer_id, order_count, avg_order_amount. Explain why WHERE cannot be used to filter aggregated groups and why HAVING is needed.

MediumTechnical

0 practiced

Orders table includes some rows with amount = NULL for refunded/cancelled orders. Write a query to compute average order amount per customer where NULL amounts should be treated as zero. Return customer_id and adjusted_avg. Explain the difference between AVG(amount) and AVG(COALESCE(amount,0)).

EasyTechnical

0 practiced

Schema:

orders(order_id, customer_id, occurred_at timestamp)

Write a SQL query that returns daily unique customers (distinct count) for the past 14 days: columns day, unique_customers. Use COUNT(DISTINCT ...) and explain how COUNT and COUNT(DISTINCT) handle NULL values and duplicates.

MediumTechnical

0 practiced

Design a SQL approach to aggregate hierarchical geography sales: country -> region -> city. Provide an example using ROLLUP or GROUPING SETS to produce subtotals at each level and explain how to display hierarchy in a BI tool that consumes this result.

HardTechnical

0 practiced

You used HAVING MAX(order_date) > '2024-01-01' to filter groups, but an analyst raises concerns about ambiguous semantics and performance. Explain potential pitfalls and show a safer rewrite (e.g., using a subquery that precomputes max per group).

Unlock Full Question Bank

Get access to hundreds of Aggregation Functions and Group By interview questions and detailed answers.

Join thousands of developers preparing for their dream job.