Mini Challenge 4 — LIMIT, Aggregate Functions, GROUP BY
title: “Mini Challenge 4 — Topics 10-12” part: 1 topic_number: 0 slug: “mini-challenge-04” difficulty: “Intermediate” prerequisites: “limit, aggregate-functions, group-by” —
Mini Challenge 4 — LIMIT, Aggregate Functions, GROUP BY
Overview
Apply your knowledge of result limiting, aggregation, and grouping to solve practical scenarios.
Challenge 1: Top 5 Projects by Budget
Find the 5 most expensive projects (highest budget). Show project_name and budget, sorted by budget descending.
Hint
SELECT, ORDER BY budget DESC, LIMIT 5.Challenge 2: Department Headcounts
Count how many employees work in each department. Show department_id and employee_count, sorted by count (highest first). Only show departments with at least 2 employees.
Hint
SELECT department_id, COUNT(*), GROUP BY, HAVING COUNT(*) >= 2, ORDER BY.Challenge 3: Salary Statistics per Department
For each department, calculate:
- Total number of employees
- Average salary (rounded to 2 decimals)
- Minimum salary
- Maximum salary
- Total payroll (sum of all salaries)
Show department_id and all five metrics. Sort by average salary descending.
Hint
SELECT department_id, COUNT, AVG, MIN, MAX, SUM, GROUP BY, ORDER BY AVG DESC.Challenge 4: Project Assignment Statistics
Using the employee_projects table, find:
- How many unique employees are assigned across all projects
- How many unique projects have at least one employee assigned
- Total hours allocated across all assignments
- Average hours per assignment
Hint
Single SELECT with COUNT(DISTINCT employee_id), COUNT(DISTINCT project_id), SUM(hours), AVG(hours). No GROUP BY needed - these are overall stats.Challenge 5: Second and Third Highest Salaries
Find the 2nd and 3rd highest unique salaries in the company. Show just the salary values, no names.
Hint
SELECT DISTINCT salary, ORDER BY DESC, LIMIT 2 OFFSET 1 (skip the 1st highest, get next 2).Answer Key
Challenge 1 Answer
```sql SELECT project_name, budget FROM projects ORDER BY budget DESC LIMIT 5; ``` **Expected output:** | project_name | budget | |--------------|---------| | Office Expansion | 1000000.00 | | Mobile App Launch | 500000.00 | | Cloud Infrastructure | 450000.00 | | Marketing Campaign Q1 | 380000.00 | | Data Pipeline | 350000.00 | Top 5 most expensive projects.Challenge 2 Answer
```sql SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id HAVING COUNT(*) >= 2 ORDER BY employee_count DESC; ``` **Expected output:** | department_id | employee_count | |---------------|----------------| | 1 | 5 | | 2 | 3 | | 3 | 3 | | 4 | 2 | | 5 | 2 | Departments with 2+ employees, largest first.Challenge 3 Answer
```sql SELECT department_id, COUNT(*) AS employee_count, ROUND(AVG(salary), 2) AS avg_salary, MIN(salary) AS min_salary, MAX(salary) AS max_salary, SUM(salary) AS total_payroll FROM employees WHERE salary IS NOT NULL GROUP BY department_id ORDER BY avg_salary DESC; ``` **Expected output:** | department_id | employee_count | avg_salary | min_salary | max_salary | total_payroll | |---------------|----------------|------------|------------|------------|---------------| | 1 | 5 | 101000.00 | 88000.00 | 120000.00 | 505000.00 | | 7 | 1 | 105000.00 | 105000.00 | 105000.00 | 105000.00 | | 10 | 1 | 98000.00 | 98000.00 | 98000.00 | 98000.00 | | 5 | 2 | 90000.00 | 82000.00 | 98000.00 | 180000.00 | Complete salary breakdown per department.Challenge 4 Answer
```sql SELECT COUNT(DISTINCT employee_id) AS unique_employees, COUNT(DISTINCT project_id) AS unique_projects, SUM(hours_allocated) AS total_hours, ROUND(AVG(hours_allocated), 2) AS avg_hours_per_assignment FROM employee_projects; ``` **Expected output:** | unique_employees | unique_projects | total_hours | avg_hours_per_assignment | |------------------|-----------------|-------------|--------------------------| | 15 | 12 | 3850 | 110.00 | Overall project assignment statistics. **Note:** Your numbers may vary based on how many assignments exist in your data.Challenge 5 Answer
```sql SELECT DISTINCT salary FROM employees WHERE salary IS NOT NULL ORDER BY salary DESC LIMIT 2 OFFSET 1; ``` **Expected output:** | salary | |---------| | 110000.00 | | 105000.00 | 2nd and 3rd highest salaries (skipped the 1st with OFFSET). **Explanation:** - DISTINCT ensures unique salary values - ORDER BY DESC sorts highest first - OFFSET 1 skips the first row (highest salary) - LIMIT 2 takes the next 2 rows (2nd and 3rd highest)Key Takeaways
• LIMIT controls result size (perfect for “top N” queries)
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarize data
• GROUP BY splits data into categories before aggregating
• DISTINCT counts unique values
• OFFSET skips rows (useful with LIMIT for pagination)
• Combine these tools for powerful analytical queries
Up Next
Next topic: HAVING vs WHERE → part1_13_having_vs_where.md
Learn to filter grouped results!