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

Scenario: You must decide whether to perform heavy aggregations in the data warehouse during ETL or defer to the reporting layer at query time. List pros/cons and recommend an approach for monthly summary metrics that are queried frequently but updated daily. Consider compute cost, storage, reuse, and fresh data needs.

EasyTechnical

0 practiced

Table:

purchases(customer_id, purchase_date timestamp, amount numeric)

Write SQL to return each customer_id with their first_purchase_date and last_purchase_date using aggregate functions. Return customer_id, first_purchase_date, last_purchase_date. Discuss NULL handling when a customer has no purchases in the table.

MediumTechnical

0 practiced

In Power BI you observe that a visual shows the average of per-row AOV rather than the average of monthly AOVs, creating misleading results. Describe how to create a DAX measure that first computes monthly AOV and then averages those monthly values across the selected period (i.e., AVERAGEX over months). Provide the DAX measure skeleton.

MediumTechnical

0 practiced

In Tableau you must display average order value by region, but the data contains NULL region values and multiple granularities. Explain how you would (1) handle NULL regions in the viz, (2) ensure the AVG is computed at the correct granularity, and (3) show steps or calculations you would use (LOD expressions if needed).

HardTechnical

0 practiced

Describe an algorithm and SQL pattern to maintain an incremental aggregate table daily_revenue_by_product(product_id, date, revenue) using upserts/merges when new raw events arrive and late events can appear. Include watermarking, idempotency, and backfill considerations.

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.