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.

HardTechnical

0 practiced

You have a 1TB orders table frequently queried for monthly revenue by country. Describe architectural and query-level strategies to speed up these GROUP BY queries: consider partitioning, clustering/sort keys, materialized views, columnar formats, and approximate aggregations. Explain trade-offs for maintenance and storage.

MediumTechnical

0 practiced

Explain how columnar OLAP databases like BigQuery and Snowflake optimize GROUP BY queries. Describe features such as column pruning, vectorized execution, clustering/partition pruning, and how they affect aggregation performance and cost.

HardTechnical

0 practiced

You run EXPLAIN ANALYZE on a GROUP BY query and see a costly HashAggregate that spills to disk and uses lots of memory. For grouping 200M rows by user_id, list practical optimizations in Postgres or ETL to reduce memory footprint and runtime (configuration, query rewrite, partitioning, pre-aggregation).

MediumTechnical

0 practiced

Write a SQL query to generate monthly revenue including months with zero revenue between two given dates. Use generate_series to produce months, then LEFT JOIN aggregated revenue. Table: orders(order_id, order_date, total_amount). Include date range parameters start_date and end_date.

MediumTechnical

0 practiced

Write a SQL query that returns, for each customer, their most recent order_id and that order's total revenue. Use LATERAL or window functions. Provide table schemas: customers(customer_id), orders(order_id, customer_id, order_date), order_items(order_item_id, order_id, price).

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.