A company wants to analyze employee turnover rates to improve workforce planning. Write a SQL query that calculates the monthly turnover rate for each department, based on employee join and departure dates. The data is stored in two tables: employees and departments.
employees table with the departments table.employees (id, department_id, join_date, leave_date)
departments (id, name)
| id | department_id | join_date | leave_date |
|---|---|---|---|
| 1 | 1 | 2022-01-15 | 2023-06-01 |
| 2 | 1 | 2022-03-10 | NULL |
| 3 | 2 | 2022-02-20 | 2023-05-15 |
| 4 | 2 | 2022-04-25 | NULL |
| 5 | 3 | 2022-01-30 | 2023-01-01 |
departments
| id | name |
|---|---|
| 1 | Sales |
| 2 | Marketing |
| 3 | HR |
| department_name | month | turnover_rate |
|---|---|---|
| Sales | 2023-06-01 | 50.00 |
| Marketing | 2023-05-01 | 50.00 |