Aggregations
Beginner
Average Salary by Department
Calculate the average salary for each department. Return columns: department_id, avg_salary. Order results by: department_id.
10 XPSolve challenge
Interactive SQL practice
SQL Aggregation Exercises
Practice aggregate functions, grouped reporting, and grain-safe metrics. These 9 interactive challenges use real schemas and instant query validation.
9 SQL challenges
Open Practice ArenaAggregations
Beginner
Completed Orders Count
Count how many orders have the status 'completed'. Return columns: completed_count.
10 XPSolve challenge
Aggregations
Beginner
Total Department Budget
Calculate the combined budget of all departments. Return columns: total_budget.
10 XPSolve challenge
Aggregations
Intermediate
Low Stock Value
Calculate inventory value for products with fewer than 200 units. Return columns: name, stock, price, inventory_value. Order results by: inventory_value DESC.
25 XPSolve challenge
Aggregations
Advanced
PRO
Weighted Average Selling Price by Region
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.
50 XPSolve challenge
Aggregations
Advanced
PRO
Conditional Distinct Customers Across Channels
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.
50 XPSolve challenge
Aggregations
Expert
PRO
Revenue Share Without Join Inflation
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.
100 XPSolve challenge
Aggregations
Expert
PRO
Quarterly Targets with Partial-Period Performance
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.
100 XPSolve challenge
Aggregations
Expert
PRO
Inventory Stockout Duration by Product
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.
100 XPSolve challenge
Concept guide
How to practice SQL Aggregation
SQL aggregation summarizes many rows with functions such as COUNT, SUM, AVG, MIN and MAX. Correct aggregation starts by defining the intended result grain.
What to master
- • Define what one output row represents
- • Understand COUNT(*) versus COUNT(column)
- • Handle NULL values in aggregate functions
- • Prevent accidental double counting after JOINs
A reliable problem-solving workflow
- 1State the metric and grouping dimensions.
- 2Inspect the source grain before aggregating.
- 3Apply filters and calculate the aggregate.
- 4Validate totals against a smaller sample or independent query.
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL.
Why is my SUM too large after a JOIN?
The JOIN may multiply source rows before aggregation. Inspect the joined row count and aggregate each side at the correct grain when necessary.