Covers SQL grouping and aggregation concepts used to summarize data across rows. Key skills include using GROUP BY with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX, counting distinct values, and filtering grouped results with HAVING while understanding the difference between WHERE and HAVING. Candidates should demonstrate correct handling of NULL values in aggregates, grouping by expressions and multiple columns, and writing multi level aggregations using ROLLUP, CUBE, and GROUPING SETS. Also important is knowing when to use subqueries or common table expressions for intermediate aggregation, the difference between aggregate functions and window functions, and how grouping interacts with joins and data types. Interview questions may test correctness of queries, edge cases, performance considerations such as appropriate indexes and query plans, and the ability to transform business questions like who are the top customers or which categories have declining sales into correct aggregated SQL statements.
MediumTechnical
36 practiced
Given tables customers(id, region) and orders(order_id, customer_id, total_amount), write a PostgreSQL query that returns the top 3 customers by lifetime revenue within each region. Return columns: region, customer_id, total_revenue, rank. Use window functions and explain why window functions are preferred for top-N-per-group problems.
EasyTechnical
30 practiced
Explain how SQL aggregate functions treat NULL values. For SUM, AVG, COUNT, MIN, MAX: what happens when the input set contains only NULLs vs a mix of NULL and non-NULL values? How should you display aggregates in a dashboard when you want zeros instead of NULLs?
EasyTechnical
51 practiced
Some SQL dialects allow aliases in GROUP BY or ORDER BY while others do not. Explain whether you can use a SELECT alias in GROUP BY (e.g., 'SELECT DATE_TRUNC('month', ts) AS month ... GROUP BY month') across common databases (Postgres, MySQL, SQL Server). Provide a portability recommendation for writing reusable BI SQL.
HardTechnical
30 practiced
Using sales(category, subcategory, amount), write a SQL query with ROLLUP(category, subcategory) that returns columns: category, subcategory, total_amount, grouping_level (0 = detail, 1 = category subtotal, 2 = grand total). Use GROUPING_ID or equivalent to derive grouping_level and a human-readable label.
HardTechnical
37 practiced
You need to compute a 7-day rolling revenue per product for a dataset with 200M rows/day. Propose efficient SQL patterns and system-level designs (e.g., window functions, incremental materialized rolling tables, time-series DBs) to compute these rolling aggregates for BI consumption, discussing latency, memory, and operational trade-offs.
Unlock Full Question Bank
Get access to hundreds of Aggregation and Grouping interview questions and detailed answers.
