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.

MediumTechnical

33 practiced

Table orders(order_id, items jsonb) where items is a JSON array of objects {"product_id":..., "price":..., "qty":...}. Write PostgreSQL SQL to extract items, unnest json arrays, and compute total revenue per product_id across all orders. Discuss performance considerations and alternatives.

EasyTechnical

36 practiced

Given a table events(user_id integer, event_type text, occurred_at timestamp) write a SQL query that returns event_type and the number of distinct users per event_type. Explain how NULL values in user_id affect COUNT(user_id), COUNT(*), and COUNT(DISTINCT user_id). Provide the SQL and a short explanation about NULL handling in COUNT.

EasyTechnical

29 practiced

Table events(user_id integer, event_type text, created_at timestamp). Write a SQL query (Postgres) that returns date (day) and count of distinct users for each day in the last 30 days. Show how to group by a date expression. Also mention (briefly) how you would include calendar days with zero counts if required.

HardTechnical

31 practiced

Design strategies to maintain incremental daily product sales aggregates when orders can arrive late and be updated. Compare full recompute, delta (CDC) based incremental updates, append-only with correction windows, and watermarking. For each approach provide pseudocode or SQL snippets and discuss consistency and operational complexity.

EasyTechnical

32 practiced

Explain the difference between aggregate functions with GROUP BY and window functions. Give a concise SQL example (Postgres) that computes for each order: order_id, amount, customer_total (aggregate per customer) using a window function, and show how the same aggregate could be returned using GROUP BY in a separate result.

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.