Aggregation and Grouping Questions

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.

EasyTechnical

34 practiced

Explain the difference between WHERE and HAVING in SQL. Using a table orders(order_id, customer_id, amount numeric), provide two example SQL queries: one that filters rows with amount > 100 before aggregation and one that filters groups having COUNT(*) > 5 after aggregation. Explain why aggregate functions cannot be used in WHERE and when HAVING is required.

MediumTechnical

31 practiced

Write a query using GROUPING SETS to compute monthly and yearly totals in a single pass. Table: transactions(txn_date date, amount numeric). Return rows tagged with whether they are month-level or year-level totals. Show SQL (Postgres) and explain grouping flags.

HardTechnical

28 practiced

High-cardinality grouping on user_id is slow. List and explain practical strategies to speed GROUP BY on high-cardinality columns: columnar storage, dictionary encoding, bitmap indexes, approximate aggregates, pre-aggregation, partitioning, and functional indexes. For each, describe when it's appropriate and limitations.

EasyTechnical

38 practiced

Tables: products(product_id, category text, subcategory text) and order_items(order_item_id, order_id, product_id, quantity integer, unit_price numeric). Write a SQL query to compute total revenue per category and subcategory for the month of 2024-09. Include columns: category, subcategory, total_revenue. Show how you JOIN and GROUP BY multiple columns.

HardTechnical

28 practiced

Compute a 7-day moving average per user for metric value in events(user_id, event_date date, value numeric), where some dates are missing per user. Provide Postgres SQL that fills missing dates per user (e.g., using generate_series or a calendar), aggregates per day, and then computes the 7-day rolling average. Discuss performance on millions of users and alternatives (approximate/windowed aggregations in Spark).

Unlock Full Question Bank

Get access to hundreds of Aggregation and Grouping interview questions and detailed answers.

Join thousands of developers preparing for their dream job.