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

In BigQuery, explain why COUNT(DISTINCT user_id) can be expensive for high-cardinality user bases. Provide an example query using an approximate function (e.g., APPROX_COUNT_DISTINCT) and explain the accuracy/performance trade-offs.

MediumTechnical

0 practiced

Write a SQL query using a derived table (CTE) to compute the top 10 customers by revenue and then join back to customers(customer_id, name, signup_date) to show customer details and their total_revenue. Explain why using a derived table can be advantageous for readability and performance.

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)).

HardTechnical

0 practiced

You must produce a report showing top products by sales, but the underlying sales table has inconsistent product_id values (legacy SKUs, mappings). Describe a data-cleaning and aggregation strategy: map legacy SKUs to canonical products, deduplicate, and ensure aggregates reflect canonical products. Provide SQL pattern and notes on storing mappings.

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.

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.