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

42 practiced

COUNT(DISTINCT user_id) on a huge table is slow. List and explain practical strategies to speed up distinct counts for very large datasets in systems like BigQuery, Redshift, and Spark. Include approximate methods and pre-aggregation approaches.

MediumTechnical

43 practiced

Write a SQL query that computes the percentage contribution of each product's monthly revenue to the total monthly revenue for that month. Use tables: orders(order_date), order_items(product_id, order_id, price). Output: month, product_id, revenue, pct_of_month.

MediumTechnical

60 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.

EasyTechnical

45 practiced

You have two tables: customers(customer_id INT, name TEXT) and orders(order_id INT, customer_id INT, order_date TIMESTAMP, total_amount NUMERIC). In PostgreSQL, write a SQL query that returns for every customer: customer_id, name, total_revenue (SUM(total_amount)), and order_count (COUNT(*)). Include customers with zero orders and order results by total_revenue descending.

MediumTechnical

49 practiced

Write a SQL query to produce monthly revenue with separate columns online_revenue and in_store_revenue using conditional aggregation. Table: orders(order_id, order_date, channel TEXT, total_amount). Output: month (YYYY-MM), online_revenue, in_store_revenue, total_revenue.

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.