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

Discuss approximate aggregate techniques for COUNT DISTINCT and quantiles. Compare HyperLogLog (HLL) for distinct counts and t-digest for quantiles in terms of mergeability, memory, and error bounds. When is using an approximate approach acceptable for business metrics?

EasyTechnical

0 practiced

Using orders(order_id, customer_id, order_date) and order_items(order_item_id, order_id, price), write a SQL query that finds customers with more than N orders in the last year. Specify N as a parameter. Make sure to filter by date properly and use HAVING where appropriate.

MediumTechnical

0 practiced

Write a single SQL query that returns for every customer: total_spend, average_order_value, order_count, and days_since_last_order. Tables: customers(customer_id), orders(order_id, customer_id, order_date, total_amount). Show how to compute recency relative to current_date.

MediumTechnical

0 practiced

Write a SQL query using GROUP BY ROLLUP (or GROUPING SETS) to compute: daily revenue per region and also a grand total row. Table: orders(order_id, order_date, total_amount), customers(customer_id, region), orders.customer_id joins customers. Explain how to identify subtotal rows.

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.

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.