Post

Mini Challenge 11 — Capstone: Complete Business Intelligence Dashboard

Posted
By Rahul Chandna
11 min read
Mini Challenge 11 — Capstone: Complete Business Intelligence Dashboard

title: “Mini Challenge 11 — Capstone Project” part: 3 topic_number: 0 slug: “mini-challenge-11” difficulty: “Advanced” prerequisites: “row-number, rank, transactions” —

Mini Challenge 11 — Capstone: Complete Business Intelligence Dashboard

Overview

Congratulations on reaching the final challenge! This capstone project combines ALL skills you’ve learned across Parts 1-3. You’ll build a comprehensive business intelligence system with window functions, ranking, and transaction safety.

Capstone Project: Executive Dashboard System

Build a complete analytics system with views, window functions, and safe data operations.

Challenge 1: Employee Performance Scorecard

Create a view employee_performance_scorecard that ranks employees using multiple metrics:

Metrics:

  • salary_rank (DENSE_RANK by salary DESC within department)
  • tenure_rank (ROW_NUMBER by hire_date ASC within department)
  • project_load_rank (RANK by project count DESC within department)
  • composite_score (average of the three ranks, lower is better)
  • performance_tier: ‘Top Performer’ if composite_score <= 3, ‘High Performer’ if <= 5, ‘Good Performer’ if <= 7, ‘Standard’ otherwise
  • compared_to_dept_avg: ‘Above’ if salary > dept avg, ‘Below’ otherwise

Show: employee_name, department_name, all ranks, composite_score, performance_tier, compared_to_dept_avg.

Sort by performance_tier, then composite_score.

Hint Join employees, departments, count projects. Use multiple window functions (DENSE_RANK, ROW_NUMBER, RANK) with PARTITION BY department. Calculate averages and composite scores. Use CASE for tiering.

Challenge 2: Payroll Adjustment Transaction

Create a safe transaction that performs a company-wide salary adjustment with these rules:

  1. Give 10% raise to employees with tenure > 5 years
  2. Give 7% raise to employees with tenure 3-5 years
  3. Give 5% raise to employees with tenure < 3 years
  4. ROLLBACK if total new payroll exceeds $2,000,000
  5. ROLLBACK if any individual salary exceeds $150,000
  6. Create a log table salary_adjustments to track changes (employee_id, old_salary, new_salary, adjustment_date, adjustment_reason)

Use savepoints to allow partial rollback if needed. Provide clear success/failure messages.

Hint START TRANSACTION, calculate tenure categories, UPDATE with CASE WHEN, check constraints, INSERT INTO log table, conditional COMMIT/ROLLBACK with savepoints.

Challenge 3: Dynamic Top Performers by Multiple Criteria

Create a view top_performers_multi_criteria that shows:

  • The top 3 employees per department by salary (include ties with DENSE_RANK)
  • The top 2 most senior employees per department (by hire_date)
  • The top 2 most productive employees per department (by project count)

Combine all three lists (UNION), showing:

  • employee_name
  • department_name
  • criterion (‘Top Salary’, ‘Most Senior’, ‘Most Productive’)
  • rank_within_criterion
  • metric_value (salary, years, or project count depending on criterion)

Sort by department_name, criterion, rank_within_criterion.

Hint Three separate CTEs with window functions (one per criterion), UNION ALL them, format output. Use DENSE_RANK to include ties.

Challenge 4: Advanced Ranking with Percentiles

Create a comprehensive ranking report:

  • employee_name, department_name, salary
  • salary_rank_overall (RANK across company)
  • salary_rank_dept (RANK within department)
  • salary_percentile (PERCENT_RANK, show as percentage 0-100)
  • salary_quintile (NTILE(5) - divide into 5 groups)
  • department_quartile (NTILE(4) within each department)
  • high_earner_flag: ‘Top 10%’ if percentile >= 90, ‘Top 25%’ if >= 75, ‘Middle 50%’ if >= 25, ‘Lower 25%’ otherwise

Sort by salary_percentile DESC.

Hint Multiple window functions: RANK (global and per dept), PERCENT_RANK, NTILE(5), NTILE(4) with PARTITION BY. Use CASE for flags.

Challenge 5: Real-World Simulation - Department Restructuring

Implement a complex transaction simulating a department merger:

Scenario: Merge “Legal” department into “Human Resources”

Requirements:

  1. Move all Legal employees to HR
  2. Update their manager_id if their current manager was also in Legal
  3. Increase their salaries by 5% (relocation bonus)
  4. Archive the Legal department (UPDATE status to ‘Inactive’ instead of DELETE)
  5. Create audit trail in restructuring_log table (department_id, action, timestamp, affected_employee_count)
  6. ROLLBACK if:
    • Any employee’s new salary > $100,000
    • Total HR department size > 10 employees
    • Any orphaned manager_id references

Use savepoints for each major step. Provide detailed success/failure report.

Hint START TRANSACTION. Multiple savepoints (sp_move, sp_managers, sp_salaries, sp_archive). Check constraints after each step. Comprehensive validation query before COMMIT. INSERT audit records.

Answer Key

Challenge 1 Answer ```sql CREATE OR REPLACE VIEW employee_performance_scorecard AS WITH employee_metrics AS ( SELECT e.employee_id, CONCAT(e.first_name, ' ', e.last_name) AS employee_name, d.department_name, e.salary, e.hire_date, TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS tenure_years, COUNT(ep.project_id) AS project_count, DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank, ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date ASC) AS tenure_rank, RANK() OVER (PARTITION BY e.department_id ORDER BY COUNT(ep.project_id) DESC) AS project_load_rank, AVG(e.salary) OVER (PARTITION BY e.department_id) AS dept_avg_salary FROM employees e JOIN departments d ON e.department_id = d.department_id LEFT 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.salary, e.hire_date, e.department_id ) SELECT employee_name, department_name, salary, salary_rank, tenure_rank, project_load_rank, ROUND((salary_rank + tenure_rank + project_load_rank) / 3.0, 2) AS composite_score, CASE WHEN (salary_rank + tenure_rank + project_load_rank) / 3.0 <= 3 THEN 'Top Performer' WHEN (salary_rank + tenure_rank + project_load_rank) / 3.0 <= 5 THEN 'High Performer' WHEN (salary_rank + tenure_rank + project_load_rank) / 3.0 <= 7 THEN 'Good Performer' ELSE 'Standard' END AS performance_tier, CASE WHEN salary > dept_avg_salary THEN 'Above' ELSE 'Below' END AS compared_to_dept_avg FROM employee_metrics ORDER BY CASE performance_tier WHEN 'Top Performer' THEN 1 WHEN 'High Performer' THEN 2 WHEN 'Good Performer' THEN 3 ELSE 4 END, composite_score; -- Use the view SELECT * FROM employee_performance_scorecard LIMIT 10; ``` Comprehensive performance metrics combining multiple factors.
Challenge 2 Answer ```sql -- Create log table CREATE TABLE IF NOT EXISTS salary_adjustments ( adjustment_id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT, old_salary DECIMAL(10,2), new_salary DECIMAL(10,2), adjustment_percentage DECIMAL(5,2), adjustment_date DATETIME DEFAULT CURRENT_TIMESTAMP, adjustment_reason VARCHAR(200) ); -- Transaction START TRANSACTION; -- Store old salaries for logging CREATE TEMPORARY TABLE salary_backup AS SELECT employee_id, salary, TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS tenure_years FROM employees; SAVEPOINT sp_before_adjustments; -- Apply raises based on tenure UPDATE employees e JOIN salary_backup sb ON e.employee_id = sb.employee_id SET e.salary = CASE WHEN sb.tenure_years > 5 THEN e.salary * 1.10 WHEN sb.tenure_years >= 3 THEN e.salary * 1.07 ELSE e.salary * 1.05 END; -- Check constraint 1: Total payroll SELECT @total_payroll := SUM(salary) FROM employees; IF @total_payroll > 2000000 THEN ROLLBACK TO sp_before_adjustments; SELECT CONCAT('ROLLBACK: Total payroll (', @total_payroll, ') exceeds limit') AS message; ELSE -- Check constraint 2: Individual salaries SELECT @max_individual := MAX(salary) FROM employees; IF @max_individual > 150000 THEN ROLLBACK TO sp_before_adjustments; SELECT CONCAT('ROLLBACK: Individual salary (', @max_individual, ') exceeds limit') AS message; ELSE -- Log all changes INSERT INTO salary_adjustments (employee_id, old_salary, new_salary, adjustment_percentage, adjustment_reason) SELECT e.employee_id, sb.salary, e.salary, ROUND(((e.salary - sb.salary) / sb.salary) * 100, 2), CONCAT('Annual adjustment - ', CASE WHEN sb.tenure_years > 5 THEN '10% (5+ years)' WHEN sb.tenure_years >= 3 THEN '7% (3-5 years)' ELSE '5% (<3 years)' END ) FROM employees e JOIN salary_backup sb ON e.employee_id = sb.employee_id WHERE e.salary != sb.salary; COMMIT; SELECT CONCAT('SUCCESS: Adjusted salaries for ', ROW_COUNT(), ' employees. New total payroll: $', @total_payroll) AS message; END IF; END IF; -- Cleanup DROP TEMPORARY TABLE salary_backup; ``` Safe, audited salary adjustment transaction.
Challenge 3 Answer ```sql CREATE OR REPLACE VIEW top_performers_multi_criteria AS WITH top_salaries AS ( SELECT CONCAT(e.first_name, ' ', e.last_name) AS employee_name, d.department_name, 'Top Salary' AS criterion, e.salary AS metric_value, DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank_within_criterion FROM employees e JOIN departments d ON e.department_id = d.department_id ), most_senior AS ( SELECT CONCAT(e.first_name, ' ', e.last_name) AS employee_name, d.department_name, 'Most Senior' AS criterion, TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS metric_value, ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date ASC) AS rank_within_criterion FROM employees e JOIN departments d ON e.department_id = d.department_id ), most_productive AS ( SELECT CONCAT(e.first_name, ' ', e.last_name) AS employee_name, d.department_name, 'Most Productive' AS criterion, COUNT(ep.project_id) AS metric_value, RANK() OVER (PARTITION BY e.department_id ORDER BY COUNT(ep.project_id) DESC) AS rank_within_criterion FROM employees e JOIN departments d ON e.department_id = d.department_id LEFT 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 ) SELECT * FROM top_salaries WHERE rank_within_criterion <= 3 UNION ALL SELECT * FROM most_senior WHERE rank_within_criterion <= 2 UNION ALL SELECT * FROM most_productive WHERE rank_within_criterion <= 2 ORDER BY department_name, criterion, rank_within_criterion; -- Use the view SELECT * FROM top_performers_multi_criteria; ``` Multi-dimensional top performers list.
Challenge 4 Answer ```sql SELECT CONCAT(e.first_name, ' ', e.last_name) AS employee_name, d.department_name, e.salary, RANK() OVER (ORDER BY e.salary DESC) AS salary_rank_overall, RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank_dept, ROUND(PERCENT_RANK() OVER (ORDER BY e.salary DESC) * 100, 1) AS salary_percentile, NTILE(5) OVER (ORDER BY e.salary DESC) AS salary_quintile, NTILE(4) OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS department_quartile, CASE WHEN PERCENT_RANK() OVER (ORDER BY e.salary DESC) >= 0.9 THEN 'Top 10%' WHEN PERCENT_RANK() OVER (ORDER BY e.salary DESC) >= 0.75 THEN 'Top 25%' WHEN PERCENT_RANK() OVER (ORDER BY e.salary DESC) >= 0.25 THEN 'Middle 50%' ELSE 'Lower 25%' END AS high_earner_flag FROM employees e JOIN departments d ON e.department_id = d.department_id ORDER BY salary_percentile DESC; ``` **Expected output:** | employee_name | department_name | salary | salary_rank_overall | salary_rank_dept | salary_percentile | salary_quintile | department_quartile | high_earner_flag | |---------------|-----------------|---------|---------------------|------------------|-------------------|-----------------|---------------------|------------------| | Sam Clark | Engineering | 95000.00 | 1 | 1 | 0.0 | 1 | 1 | Top 10% | | Frank Miller | Marketing | 93000.00 | 2 | 1 | 5.3 | 1 | 1 | Top 10% | | Henry Moore | Finance | 92000.00 | 3 | 1 | 10.5 | 1 | 1 | Top 25% | | ... | ... | ... | ... | ... | ... | ... | ... | ... | Advanced multi-dimensional ranking system.
Challenge 5 Answer ```sql -- Create audit table CREATE TABLE IF NOT EXISTS restructuring_log ( log_id INT AUTO_INCREMENT PRIMARY KEY, department_id INT, action VARCHAR(100), timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, affected_employee_count INT, notes TEXT ); -- Add status column to departments if doesn't exist ALTER TABLE departments ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'Active'; -- Transaction START TRANSACTION; -- Get Legal and HR department IDs SELECT @legal_dept_id := department_id FROM departments WHERE department_name = 'Legal'; SELECT @hr_dept_id := department_id FROM departments WHERE department_name = 'Human Resources'; -- Count affected employees SELECT @legal_emp_count := COUNT(*) FROM employees WHERE department_id = @legal_dept_id; SAVEPOINT sp_move_employees; -- Step 1: Move employees to HR UPDATE employees SET department_id = @hr_dept_id WHERE department_id = @legal_dept_id; SAVEPOINT sp_fix_managers; -- Step 2: Fix manager_id for moved employees whose managers were also in Legal UPDATE employees e1 SET manager_id = NULL -- Or reassign to HR manager WHERE department_id = @hr_dept_id AND manager_id IN ( SELECT employee_id FROM (SELECT employee_id FROM employees WHERE department_id = @hr_dept_id) AS moved_emps ); SAVEPOINT sp_salary_adjustment; -- Step 3: Give 5% relocation bonus UPDATE employees SET salary = salary * 1.05 WHERE department_id = @hr_dept_id; -- Constraint checks SELECT @max_salary := MAX(salary) FROM employees WHERE department_id = @hr_dept_id; SELECT @hr_size := COUNT(*) FROM employees WHERE department_id = @hr_dept_id; IF @max_salary > 100000 THEN ROLLBACK TO sp_salary_adjustment; SELECT CONCAT('ROLLBACK: Salary limit exceeded (', @max_salary, ')') AS message; ROLLBACK; ELSEIF @hr_size > 10 THEN ROLLBACK TO sp_move_employees; SELECT CONCAT('ROLLBACK: HR department too large (', @hr_size, ' employees)') AS message; ROLLBACK; ELSE SAVEPOINT sp_archive_dept; -- Step 4: Archive Legal department UPDATE departments SET status = 'Inactive' WHERE department_id = @legal_dept_id; -- Step 5: Log the restructuring INSERT INTO restructuring_log (department_id, action, affected_employee_count, notes) VALUES (@legal_dept_id, 'Department Merged', @legal_emp_count, 'Legal merged into HR'), (@hr_dept_id, 'Received Employees', @legal_emp_count, CONCAT('Received ', @legal_emp_count, ' employees from Legal')); COMMIT; SELECT CONCAT('SUCCESS: Merged Legal into HR. Moved ', @legal_emp_count, ' employees. New HR size: ', @hr_size) AS message; -- Show final state SELECT d.department_name, d.status, COUNT(e.employee_id) AS employee_count, AVG(e.salary) AS avg_salary FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE d.department_id IN (@legal_dept_id, @hr_dept_id) GROUP BY d.department_id, d.department_name, d.status; END IF; ``` Complex department restructuring with full audit trail and safety checks.

COURSE COMPLETE!

Congratulations! You’ve mastered MySQL 8+ from beginner to advanced:

Part 1 — Foundations

INSERT, UPDATE, DELETE, table management, SELECT with filtering, JOINs, aggregates, NULL handling, string functions

Part 2 — Intermediate

SUBSTRING, CASE WHEN, self joins, multiple joins, COALESCE, nested/correlated subqueries

Part 3 — Advanced

Recursive CTEs, date/time functions, views, ROW_NUMBER/RANK/DENSE_RANK, transactions

What’s Next?

You’re now equipped to:

  • Build complex database applications
  • Perform sophisticated data analysis
  • Design efficient queries for real-world scenarios
  • Ensure data integrity with transactions
  • Create maintainable, professional SQL code

Keep learning:

  • Practice with real datasets
  • Optimize query performance (indexes, execution plans)
  • Explore MySQL 8+ features (JSON, full-text search, geospatial)
  • Build complete applications integrating SQL with your favorite programming language

**Thank you for completing this journey! Keep coding! **

Graduate Training, MySQL
Graduate Training
This post is licensed under CC BY 4.0 by the author.