GROUP BY
Beginner
Count Employees Per Department
Count the number of employees in each department. Return columns: department_id, employee_count. Order results by: department_id.
10 XP
Solve summary and reporting exercises with aggregate functions and grouped filters. These 47 interactive challenges use real schemas and instant query validation.
Count the number of employees in each department. Return columns: department_id, employee_count. Order results by: department_id.
Concept guide
SQL aggregation summarizes many rows with functions such as COUNT, SUM, AVG, MIN and MAX. Correct aggregation starts by defining the intended result grain.
COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL.
The JOIN may multiply source rows before aggregation. Inspect the joined row count and aggregate each side at the correct grain when necessary.
Calculate the average salary for each department. Return columns: department_id, avg_salary. Order results by: department_id.
Count how many orders have the status 'completed'. Return columns: completed_count.
Count customers in each country, sorted by the country name. Return columns: country, customer_count. Order results by: country.
Calculate the combined budget of all departments. Return columns: total_budget.
Find departments where the average salary is greater than 75,000. Return columns: department_id, avg_salary. Order results by: department_id.
Show the order count and total amount for each status. Return columns: status, order_count, total_amount. Order results by: status.
Find customers who placed more than one order. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Calculate inventory value for products with fewer than 200 units. Return columns: name, stock, price, inventory_value. Order results by: inventory_value DESC.
Return customers who placed at least 1 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 2 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 3 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 4 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 5 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 6 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 7 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 8 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Find customers whose total completed-order spend exceeds 250. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 750. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 900. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1200. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1800. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 2500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Summarize order statuses whose total revenue is above 250. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 750. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 900. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1200. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1800. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 2500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
For completed positive-quantity sales, calculate each region's weighted average selling price as SUM(quantity*unit_price)/SUM(quantity). Cancelled sales and return rows with negative quantity are excluded. Return region, units_sold, revenue and weighted_average_price rounded to 2 decimals. Return columns: region, units_sold, revenue, weighted_average_price.
For each month, count distinct customers with completed web activity, completed store activity, both channels, and exactly one channel. Repeated transactions must count a customer once; cancelled activity is excluded. Return columns: month, web_customers, store_customers, both_channels, exactly_one_channel.
Find customers who purchased products from both Software and Infrastructure. Return columns: customer_name, category_count. Order results by: c.name.
Find products whose order-item revenue exceeds 250. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 500. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 750. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 900. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 1200. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 1500. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 1800. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 2500. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Calculate each product's share of completed invoice revenue. Invoice lines define revenue; line_tags is one-to-many descriptive data and must not multiply line revenue. Return product_id, revenue and revenue_share. Products with multiple tags must remain at invoice-line grain. Return columns: product_id, revenue, revenue_share.
As of 2025-05-15, compare regional completed revenue with calendar-day-paced quarterly targets. Q1 is complete: 90/90 eligible days. Q2 uses April 1 through May 15 inclusive: 45/91 days. Return region, quarter, actual_revenue, quarterly_target, eligible_days, quarter_days, paced_target and attainment_rate. A zero target yields NULL attainment. Return columns: region, quarter, actual_revenue, quarterly_target, eligible_days, quarter_days, paced_target, attainment_rate.
Inventory level events set the product's level until the next event. As of 2025-01-10 00:00:00, a stockout interval begins when level becomes zero and ends at the next event changing it above zero; open stockouts end at the cutoff. Sequence same-time events by event_id. Return product_id, stockout_start, stockout_end and stockout_hours. Return columns: product_id, stockout_start, stockout_end, stockout_hours.