Post

Mini Challenge 10 — Recursive CTEs, Dates, Views

Posted
By Rahul Chandna
7 min read
Mini Challenge 10 — Recursive CTEs, Dates, Views

title: “Mini Challenge 10 — Part 3 Topics 1-3” part: 3 topic_number: 0 slug: “mini-challenge-10” difficulty: “Advanced” prerequisites: “hierarchies-recursive-cte, date-functions, views” —

Mini Challenge 10 — Recursive CTEs, Dates, Views

Overview

Apply advanced SQL techniques to solve complex real-world scenarios.

Challenge 1: Complete Org Chart with Metrics

Create a recursive CTE that shows the complete organizational hierarchy with:

  • employee_name (first + last)
  • level (depth in hierarchy)
  • path (chain of command, e.g., “Alice -> Carol -> David”)
  • direct_reports (count of immediate reports)
  • total_team_size (count of all subordinates at all levels below)
  • span_of_control: ‘Wide’ if direct_reports > 5, ‘Balanced’ if 2-5, ‘Narrow’ if 1, ‘Individual Contributor’ if 0

Sort by path.

Hint Recursive CTE for hierarchy with level and path, then join back to count subordinates, use CASE for span categorization.

Challenge 2: Anniversary Calendar

Create a view called upcoming_anniversaries that shows:

  • employee_name
  • hire_date
  • years_with_company
  • next_anniversary_date (in current year)
  • days_until_anniversary
  • anniversary_type: ‘5 Year’, ‘10 Year’, ‘15 Year’, or ‘Standard’

Only include anniversaries within the next 90 days. Sort by days_until_anniversary.

Hint DATE_ADD to calculate anniversary in current year, DATEDIFF for days until, TIMESTAMPDIFF for years, CASE for type, WHERE filter.

Challenge 3: Tenure-Based Hierarchy Levels

Create a report using a recursive CTE that shows:

  • manager_name
  • manager_tenure_years
  • level_in_org
  • report_name
  • report_tenure_years
  • tenure_gap (manager_tenure - report_tenure)
  • mentorship_readiness: ‘Ready to Lead’ if report_tenure >= 3 years, ‘Developing’ if 1-3 years, ‘New’ if < 1 year

Only include manager-report pairs where the manager has been with the company longer than the report.

Hint Start with manager hierarchy, calculate TIMESTAMPDIFF for tenure, self-join for manager-report pairs, filter WHERE manager tenure > report tenure.

Challenge 4: Department Activity Timeline

Create a view department_monthly_activity showing:

  • year_month (‘2023-01’ format)
  • department_name
  • new_hires (count)
  • active_projects (count of projects with status ‘In Progress’ that have employees from that dept)
  • total_hours_allocated (for that dept that month)

Show last 12 months of data. Sort by year_month DESC, department_name.

Hint Join employees, departments, employee_projects, projects. GROUP BY year-month and department. Use DATE_FORMAT and MONTH/YEAR functions. Filter date range.

Challenge 5: Recursive Project Dependencies

Imagine projects can depend on other projects (add a depends_on_project_id column). Create a recursive CTE that shows:

  • project_name
  • dependency_level (0 = no dependencies, 1 = depends on level-0 projects, etc.)
  • dependency_path (chain of dependencies)
  • estimated_start_date (based on predecessors finishing)
Hint ALTER TABLE to add depends_on_project_id, INSERT sample dependencies. Recursive CTE starting with projects that have no dependencies (depends_on_project_id IS NULL), recurse through dependent projects.

Answer Key

Challenge 1 Answer ```sql WITH RECURSIVE org_hierarchy AS ( -- Anchor: Top-level managers SELECT employee_id, CONCAT(first_name, ' ', last_name) AS employee_name, manager_id, 1 AS level, CAST(CONCAT(first_name, ' ', last_name) AS CHAR(500)) AS path FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive: Find reports SELECT e.employee_id, CONCAT(e.first_name, ' ', e.last_name), e.manager_id, oh.level + 1, CONCAT(oh.path, ' -> ', e.first_name, ' ', e.last_name) FROM employees e JOIN org_hierarchy oh ON e.manager_id = oh.employee_id ), team_sizes AS ( SELECT e.employee_id, COUNT(DISTINCT r.employee_id) AS direct_reports, COUNT(DISTINCT all_reports.employee_id) AS total_team_size FROM employees e LEFT JOIN employees r ON e.employee_id = r.manager_id LEFT JOIN org_hierarchy all_reports ON e.employee_id = all_reports.manager_id OR all_reports.path LIKE CONCAT('%', e.first_name, ' ', e.last_name, '%') GROUP BY e.employee_id ) SELECT oh.employee_name, oh.level, oh.path, COALESCE(ts.direct_reports, 0) AS direct_reports, COALESCE(ts.total_team_size, 0) AS total_team_size, CASE WHEN COALESCE(ts.direct_reports, 0) > 5 THEN 'Wide' WHEN COALESCE(ts.direct_reports, 0) BETWEEN 2 AND 5 THEN 'Balanced' WHEN COALESCE(ts.direct_reports, 0) = 1 THEN 'Narrow' ELSE 'Individual Contributor' END AS span_of_control FROM org_hierarchy oh LEFT JOIN team_sizes ts ON oh.employee_id = ts.employee_id ORDER BY oh.path; ``` Complete organizational structure with management metrics.
Challenge 2 Answer ```sql CREATE OR REPLACE VIEW upcoming_anniversaries AS SELECT CONCAT(first_name, ' ', last_name) AS employee_name, hire_date, TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_with_company, DATE_ADD( hire_date, INTERVAL YEAR(CURDATE()) - YEAR(hire_date) YEAR ) AS next_anniversary_date, DATEDIFF( DATE_ADD(hire_date, INTERVAL YEAR(CURDATE()) - YEAR(hire_date) YEAR), CURDATE() ) AS days_until_anniversary, CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) % 15 = 0 THEN '15 Year' WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) % 10 = 0 THEN '10 Year' WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) % 5 = 0 THEN '5 Year' ELSE 'Standard' END AS anniversary_type FROM employees WHERE DATEDIFF( DATE_ADD(hire_date, INTERVAL YEAR(CURDATE()) - YEAR(hire_date) YEAR), CURDATE() ) BETWEEN 0 AND 90 ORDER BY days_until_anniversary; -- Use the view SELECT * FROM upcoming_anniversaries; ``` **Expected output:** | employee_name | hire_date | years_with_company | next_anniversary_date | days_until_anniversary | anniversary_type | |---------------|-----------|--------------------|-----------------------|------------------------|------------------| | Bob Smith | 2020-05-10 | 6 | 2026-05-10 | 10 | Standard | | Eve Davis | 2022-05-14 | 4 | 2026-05-14 | 14 | Standard | Upcoming work anniversaries for recognition planning.
Challenge 3 Answer ```sql WITH RECURSIVE org_levels AS ( SELECT employee_id, CONCAT(first_name, ' ', last_name) AS employee_name, manager_id, hire_date, TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS tenure_years, 1 AS level_in_org FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, CONCAT(e.first_name, ' ', e.last_name), e.manager_id, e.hire_date, TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()), ol.level_in_org + 1 FROM employees e JOIN org_levels ol ON e.manager_id = ol.employee_id ) SELECT m.employee_name AS manager_name, m.tenure_years AS manager_tenure_years, m.level_in_org, r.employee_name AS report_name, r.tenure_years AS report_tenure_years, m.tenure_years - r.tenure_years AS tenure_gap, CASE WHEN r.tenure_years >= 3 THEN 'Ready to Lead' WHEN r.tenure_years >= 1 THEN 'Developing' ELSE 'New' END AS mentorship_readiness FROM org_levels m JOIN org_levels r ON m.employee_id = r.manager_id WHERE m.tenure_years > r.tenure_years ORDER BY m.employee_name, tenure_gap DESC; ``` **Expected output:** | manager_name | manager_tenure_years | level_in_org | report_name | report_tenure_years | tenure_gap | mentorship_readiness | |--------------|----------------------|--------------|-------------|---------------------|------------|----------------------| | Alice Johnson | 7 | 1 | Carol Williams | 6 | 1 | Ready to Lead | | Alice Johnson | 7 | 1 | Eve Davis | 4 | 3 | Ready to Lead | | Bob Smith | 6 | 1 | Frank Miller | 8 | -2 | Ready to Lead | Manager-report pairs with tenure analysis. **Note:** Negative tenure_gap means report has been there longer (unusual but possible).
Challenge 4 Answer ```sql CREATE OR REPLACE VIEW department_monthly_activity AS SELECT DATE_FORMAT(e.hire_date, '%Y-%m') AS year_month, d.department_name, COUNT(DISTINCT e.employee_id) AS new_hires, COUNT(DISTINCT CASE WHEN p.status = 'In Progress' THEN p.project_id END) AS active_projects, SUM(CASE WHEN p.status = 'In Progress' THEN ep.hours_allocated ELSE 0 END) AS total_hours_allocated FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id AND e.hire_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id LEFT JOIN projects p ON ep.project_id = p.project_id WHERE e.hire_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) OR e.hire_date IS NULL GROUP BY DATE_FORMAT(e.hire_date, '%Y-%m'), d.department_id, d.department_name HAVING year_month IS NOT NULL ORDER BY year_month DESC, d.department_name; -- Use the view SELECT * FROM department_monthly_activity; ``` Monthly activity dashboard per department.
Challenge 5 Answer ```sql -- Setup: Add dependency column and sample data ALTER TABLE projects ADD COLUMN depends_on_project_id INT; UPDATE projects SET depends_on_project_id = 1 WHERE project_id = 3; -- Mobile App depends on Website Redesign UPDATE projects SET depends_on_project_id = 3 WHERE project_id = 5; -- Data Pipeline depends on Mobile App -- Recursive CTE WITH RECURSIVE project_dependencies AS ( -- Anchor: Projects with no dependencies SELECT project_id, project_name, depends_on_project_id, 0 AS dependency_level, CAST(project_name AS CHAR(500)) AS dependency_path, CURDATE() AS estimated_start_date FROM projects WHERE depends_on_project_id IS NULL UNION ALL -- Recursive: Projects that depend on others SELECT p.project_id, p.project_name, p.depends_on_project_id, pd.dependency_level + 1, CONCAT(pd.dependency_path, ' -> ', p.project_name), DATE_ADD(pd.estimated_start_date, INTERVAL 90 DAY) -- 90 days after predecessor FROM projects p JOIN project_dependencies pd ON p.depends_on_project_id = pd.project_id ) SELECT project_name, dependency_level, dependency_path, estimated_start_date FROM project_dependencies ORDER BY dependency_level, project_name; ``` **Expected output:** | project_name | dependency_level | dependency_path | estimated_start_date | |--------------|------------------|-----------------|----------------------| | Website Redesign | 0 | Website Redesign | 2026-04-30 | | API Development | 0 | API Development | 2026-04-30 | | Mobile App | 1 | Website Redesign -> Mobile App | 2026-07-29 | | Data Pipeline | 2 | Website Redesign -> Mobile App -> Data Pipeline | 2026-10-27 | Project dependency chain with estimated start dates.

Key Takeaways

• Recursive CTEs traverse hierarchies and dependencies • Date functions enable temporal analytics and scheduling • Views encapsulate complex logic for reuse • Combining techniques creates powerful business solutions • Always consider performance with deep recursions • Test edge cases (NULLs, missing data, circular references)

Up Next

Next topic: ROW_NUMBER() and Window Functions → part3_04_row_number.md

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