Mini Challenge 9 — COALESCE, Subqueries
Mini Challenge 9 — COALESCE, Subqueries
title: “Mini Challenge 9 — Part 2 Topics 5-7” part: 2 topic_number: 0 slug: “mini-challenge-09” difficulty: “Intermediate” prerequisites: “coalesce, nested-subqueries, correlated-subqueries” —
Mini Challenge 9 — COALESCE, Subqueries
Overview
Master NULL handling, nested queries, and correlated subqueries through complex scenarios.
Challenge 1: Contact Consolidation
Create a comprehensive contact report:
- first_name, last_name
- primary_contact: email if available, else mobile_phone, else office_phone, else ‘UPDATE REQUIRED’
- contact_type: ‘Email’, ‘Mobile’, ‘Office’, or ‘Missing’
- backup_contact: office_phone if primary is email/mobile, mobile_phone if primary is office, else ‘None Available’
Sort by contact_type (Missing first to prioritize fixes), then last_name.
Hint
Multiple COALESCE calls, CASE WHEN to determine contact_type and backup logic.Challenge 2: Above-Average Performers
Find employees earning more than the average salary of ALL employees who were hired in the same year as them. Show:
- first_name, last_name
- hire_year
- salary
- avg_salary_same_year (average for their cohort)
- salary_advantage (their salary - average)
Sort by hire_year, then salary_advantage DESC.
Hint
Correlated subquery: WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE YEAR(e2.hire_date) = YEAR(e.hire_date))Challenge 3: Department Dominance
Find departments where EVERY employee earns more than $65,000. Show department_name, employee_count, and min_salary.
Hint
NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM employees WHERE department_id = d.department_id AND salary <= 65000)Challenge 4: Project Assignment Gaps
Find employees who are assigned to projects but have fewer total hours_allocated than the average hours for their department. Show:
- first_name, last_name
- department_name
- their_total_hours
- dept_avg_hours
- hours_below_average
Only include employees with project assignments. Sort by hours_below_average DESC (biggest gaps first).
Hint
Join employees -> employee_projects, SUM hours, then use correlated subquery or derived table for department average.Challenge 5: Compensation Fairness Analysis
Find employees whose total compensation (salary + commission) is more than 20% different from others in their department. Show:
- first_name, last_name
- total_compensation (use COALESCE for commission, default to 0)
- department_avg_compensation (excluding the employee themselves)
- percent_difference (absolute percentage difference)
- fairness_flag: ‘Significantly Over’ if > 20% above avg, ‘Significantly Under’ if > 20% below avg
Sort by percent_difference DESC.
Hint
Correlated subquery to calculate avg total compensation for department (excluding self), calculate percentage, use CASE for flags.Answer Key
Challenge 1 Answer
```sql SELECT first_name, last_name, COALESCE(email, mobile_phone, office_phone, 'UPDATE REQUIRED') AS primary_contact, CASE WHEN email IS NOT NULL THEN 'Email' WHEN mobile_phone IS NOT NULL THEN 'Mobile' WHEN office_phone IS NOT NULL THEN 'Office' ELSE 'Missing' END AS contact_type, CASE WHEN email IS NOT NULL OR mobile_phone IS NOT NULL THEN COALESCE(office_phone, 'None Available') WHEN office_phone IS NOT NULL THEN COALESCE(mobile_phone, 'None Available') ELSE 'None Available' END AS backup_contact FROM employees ORDER BY CASE contact_type WHEN 'Missing' THEN 1 WHEN 'Office' THEN 2 WHEN 'Mobile' THEN 3 WHEN 'Email' THEN 4 END, last_name; ``` **Expected output:** | first_name | last_name | primary_contact | contact_type | backup_contact | |------------|-----------|-----------------|--------------|----------------| | Sam | Clark | UPDATE REQUIRED | Missing | None Available | | Alice | Johnson | [email protected] | Email | 555-1001 | | Bob | Smith | [email protected] | Email | None Available | | ... | ... | ... | ... | ... | Prioritized contact information with backups.Challenge 2 Answer
```sql SELECT e.first_name, e.last_name, YEAR(e.hire_date) AS hire_year, e.salary, (SELECT ROUND(AVG(salary), 2) FROM employees e2 WHERE YEAR(e2.hire_date) = YEAR(e.hire_date)) AS avg_salary_same_year, ROUND(e.salary - (SELECT AVG(salary) FROM employees e2 WHERE YEAR(e2.hire_date) = YEAR(e.hire_date)), 2) AS salary_advantage FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees e2 WHERE YEAR(e2.hire_date) = YEAR(e.hire_date) ) ORDER BY hire_year, salary_advantage DESC; ``` **Expected output:** | first_name | last_name | hire_year | salary | avg_salary_same_year | salary_advantage | |------------|-----------|-----------|---------|----------------------|------------------| | George | Jones | 2017 | 71000.00 | 70000.00 | 1000.00 | | Frank | Miller | 2018 | 93000.00 | 80000.00 | 13000.00 | | Alice | Johnson | 2019 | 75000.00 | 73000.00 | 2000.00 | | ... | ... | ... | ... | ... | ... | Top earners within each hiring cohort.Challenge 3 Answer
```sql SELECT d.department_name, COUNT(e.employee_id) AS employee_count, MIN(e.salary) AS min_salary FROM departments d JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name HAVING NOT EXISTS ( SELECT 1 FROM employees e2 WHERE e2.department_id = d.department_id AND e2.salary <= 65000 ) ORDER BY min_salary DESC; ``` **Expected output:** | department_name | employee_count | min_salary | |-----------------|----------------|------------| | Marketing | 2 | 82000.00 | | Engineering | 3 | 75000.00 | | Finance | 2 | 66000.00 | Departments where everyone earns above threshold. **Alternative using MIN in HAVING:** ```sql SELECT d.department_name, COUNT(e.employee_id) AS employee_count, MIN(e.salary) AS min_salary FROM departments d JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name HAVING MIN(e.salary) > 65000 ORDER BY min_salary DESC; ```Challenge 4 Answer
```sql SELECT e.first_name, e.last_name, d.department_name, SUM(ep.hours_allocated) AS their_total_hours, (SELECT ROUND(AVG(total_hrs.hours), 2) FROM ( SELECT e2.employee_id, SUM(ep2.hours_allocated) AS hours FROM employees e2 JOIN employee_projects ep2 ON e2.employee_id = ep2.employee_id WHERE e2.department_id = e.department_id GROUP BY e2.employee_id ) AS total_hrs ) AS dept_avg_hours, (SELECT ROUND(AVG(total_hrs.hours), 2) FROM ( SELECT e2.employee_id, SUM(ep2.hours_allocated) AS hours FROM employees e2 JOIN employee_projects ep2 ON e2.employee_id = ep2.employee_id WHERE e2.department_id = e.department_id GROUP BY e2.employee_id ) AS total_hrs ) - SUM(ep.hours_allocated) AS hours_below_average FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN employee_projects ep ON e.employee_id = ep.employee_id GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name, e.department_id HAVING SUM(ep.hours_allocated) < ( SELECT AVG(total_hrs.hours) FROM ( SELECT e2.employee_id, SUM(ep2.hours_allocated) AS hours FROM employees e2 JOIN employee_projects ep2 ON e2.employee_id = ep2.employee_id WHERE e2.department_id = e.department_id GROUP BY e2.employee_id ) AS total_hrs ) ORDER BY hours_below_average DESC; ``` **Expected output:** | first_name | last_name | department_name | their_total_hours | dept_avg_hours | hours_below_average | |------------|-----------|-----------------|-------------------|----------------|---------------------| | David | Brown | Finance | 100 | 170.00 | 70.00 | | Carol | Williams | Sales | 140 | 200.00 | 60.00 | Underutilized employees (compared to dept peers).Challenge 5 Answer
```sql SELECT e.first_name, e.last_name, e.salary + COALESCE(e.commission, 0) AS total_compensation, (SELECT ROUND(AVG(e2.salary + COALESCE(e2.commission, 0)), 2) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.employee_id != e.employee_id ) AS department_avg_compensation, ROUND( ABS( (e.salary + COALESCE(e.commission, 0)) - (SELECT AVG(e2.salary + COALESCE(e2.commission, 0)) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.employee_id != e.employee_id) ) / (SELECT AVG(e2.salary + COALESCE(e2.commission, 0)) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.employee_id != e.employee_id) * 100, 2 ) AS percent_difference, CASE WHEN (e.salary + COALESCE(e.commission, 0)) > (SELECT AVG(e2.salary + COALESCE(e2.commission, 0)) * 1.2 FROM employees e2 WHERE e2.department_id = e.department_id AND e2.employee_id != e.employee_id) THEN 'Significantly Over' WHEN (e.salary + COALESCE(e.commission, 0)) < (SELECT AVG(e2.salary + COALESCE(e2.commission, 0)) * 0.8 FROM employees e2 WHERE e2.department_id = e.department_id AND e2.employee_id != e.employee_id) THEN 'Significantly Under' ELSE 'Fair' END AS fairness_flag FROM employees e HAVING percent_difference > 20 ORDER BY percent_difference DESC; ``` **Expected output:** | first_name | last_name | total_compensation | department_avg_compensation | percent_difference | fairness_flag | |------------|-----------|--------------------|-----------------------------|-------------------|---------------| | Sam | Clark | 95000.00 | 76000.00 | 25.00 | Significantly Over | | Bob | Smith | 82000.00 | 73500.00 | 11.56 | Fair | | ... | ... | ... | ... | ... | ... | Identifies compensation outliers for fairness review.Key Takeaways
• COALESCE chains fallbacks for robust NULL handling • Nested subqueries filter and calculate dynamically • Correlated subqueries enable row-by-row comparisons • NOT EXISTS checks for absence of conditions • Combining techniques creates sophisticated analytics • Always exclude self in comparative aggregates for accuracy
Part 2 Complete!
Ready for advanced SQL topics in Part 3?
Up Next
Part 3: Hierarchies Using Recursive CTEs → part3_01_hierarchies_recursive_cte.md
This post is licensed under
CC BY 4.0
by the author.