10 SQL Group By Interview Questions and Answers

SQL’s GROUP BY clause is a fundamental tool for data aggregation and analysis, enabling users to summarize large datasets efficiently. By grouping rows that share a common attribute, GROUP BY allows for the calculation of aggregate functions such as COUNT, SUM, AVG, MAX, and MIN, making it indispensable for generating meaningful insights from raw data. Mastery of this clause is essential for anyone working with relational databases, as it forms the backbone of many complex queries and reports.

This article provides a curated selection of interview questions focused on the GROUP BY clause, designed to test and enhance your understanding of its applications and nuances. Reviewing these questions will help you build confidence and demonstrate your proficiency in SQL during technical interviews.

SQL Group By Interview Questions and Answers

1. Write a query to group employees by their department and count the number of employees in each department.

To group employees by their department and count the number of employees in each department, use the GROUP BY clause with the COUNT() function. This aggregates data based on the department.

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

2. Write a query to find the total sales amount for each product category.

To find the total sales amount for each product category, use the GROUP BY clause with the SUM function.

Example:

SELECT product_category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_category;

This query groups the sales table by product_category and calculates the total sales amount for each category.

3. Write a query to group data by both city and state and calculate the average income for each group.

To group data by both city and state and calculate the average income for each group, use the GROUP BY clause.

Example:

SELECT city, state, AVG(income) AS average_income
FROM employees
GROUP BY city, state;

This query selects the columns and applies the aggregate function, grouping by city and state.

4. Write a query to group orders by month and year and find the total sales for each month.

To group orders by month and year and find the total sales for each month, use the GROUP BY clause.

Example:

SELECT 
    YEAR(order_date) AS order_year, 
    MONTH(order_date) AS order_month, 
    SUM(total_amount) AS total_sales
FROM 
    orders
GROUP BY 
    YEAR(order_date), 
    MONTH(order_date)
ORDER BY 
    order_year, 
    order_month;

5. Write a query to join two tables and group the results by a column from one of the tables.

To join two tables and group the results by a column from one of the tables, use the JOIN and GROUP BY clauses.

Example:

SELECT c.customer_name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

This query joins the customers and orders tables and groups the results by customer_name.

6. Write a query to perform conditional aggregation within groups, such as counting only specific types of records.

Conditional aggregation within groups allows calculations on subsets of data using the GROUP BY clause with conditional expressions.

Example:

SELECT 
    department,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM 
    employees
GROUP BY 
    department;

This query groups by department and counts active and inactive employees using CASE statements.

7. Write a query that uses GROUP BY with rollup or cube operations to provide subtotals and grand totals.

The GROUP BY clause with ROLLUP or CUBE operations generates subtotals and grand totals.

Example using ROLLUP:

SELECT department, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP(department, product);

Example using CUBE:

SELECT department, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY CUBE(department, product);

These queries produce results with various levels of aggregation.

8. Explain the concept of grouping sets and write a query that demonstrates its use.

Grouping sets allow multiple groupings in a single query, simplifying complex reports.

Example:

SELECT 
    department, 
    product, 
    SUM(sales) AS total_sales
FROM 
    sales_data
GROUP BY 
    GROUPING SETS (
        (department, product),
        (department),
        ()
    );

This query creates groupings for department and product combinations, department alone, and a grand total.

9. Discuss how indexes can impact the performance of GROUP BY queries.

Indexes can enhance the performance of GROUP BY queries by allowing faster data retrieval. The effectiveness of an index depends on factors like the type of index and the selectivity of the indexed columns. A composite index covering all GROUP BY columns can be particularly beneficial. However, indexes also incur storage overhead and can slow down write operations, so their use should be carefully considered.

10. Write a query to optimize a GROUP BY operation on a large dataset using indexing or other techniques.

Optimizing a GROUP BY operation on a large dataset can improve query performance. Indexing is one effective method.

Example:

-- Create an index on the column(s) used in the GROUP BY clause
CREATE INDEX idx_column_name ON table_name(column_name);

-- Optimized GROUP BY query
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

In addition to indexing, consider partitioning, query optimization, and materialized views to enhance performance.