FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    hire_date DATE
);
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(50),
    department VARCHAR(50)
);
CREATE TABLE assignments (
    employee_id INT,
    project_id INT,
    start_date DATE,
    end_date DATE,
    PRIMARY KEY (employee_id, project_id));

INSERT INTO employees (employee_id, employee_name, department, hire_date) VALUES
(1001, 'Jake B', 'IT', '2020-01-01'),
(1002, 'Alice Wonder', 'Marketing', '2021-03-15'),
(1003, 'Jack Johnson', 'Finance', '2019-06-30'),
(1004, 'Emily Blake', 'HR', '2022-09-01'),
(1005, 'David Wick', 'IT', '2018-11-20'),
(1006, 'Mitchel Jack', 'Finance', '2019-11-20'),
(1007, 'Drew J', 'IT', '2020-01-20'),
(1008, 'Mary Jil', 'IT', '2021-01-17');

INSERT INTO projects (project_id, project_name, department) VALUES
(501, 'Website Redesign', 'IT'),
(502, 'Social Media Campaign', 'Marketing'),
(503, 'Financial Reporting System', 'Finance'),
(504, 'Employee Onboarding', 'HR'),
(505, 'Cloud Migration', 'IT');

INSERT INTO assignments (employee_id, project_id, start_date, end_date) VALUES
(1001, 501, '2024-01-01', '2024-03-31'),
(1001, 505, '2024-04-01', '2024-06-30'),
(1002, 502, '2024-02-15', '2024-05-31'),
(1003, 503, '2024-01-01', '2024-06-30'),
(1004, 504, '2024-03-01', '2024-04-30'),
(1005, 501, '2024-02-01', '2024-04-30'),
(1005, 505, '2024-05-01', '2024-07-31'), 
(1006, 503, '2024-03-01', '2024-03-30'),
(1007, 505, '2024-02-01', '2024-06-30'),
(1008, 501, '2024-01-01', '2024-07-31');

WITH EmployeeProjectDuration AS (
  SELECT e.employee_id, e.employee_name, 
         AVG(DATEDIFF(day, a.start_date, a.end_date)) AS avg_project_duration --Calculaute Average Duration
  FROM employees e
  INNER JOIN assignments a ON e.employee_id = a.employee_id
  INNER JOIN projects p ON a.project_id = p.project_id
  WHERE p.department = 'IT' --- Filter only employee in "IT"
  GROUP BY e.employee_id, e.employee_name
),
Ranked_Employee AS (
SELECT employee_id, employee_name, avg_project_duration,
       RANK() OVER (ORDER BY avg_project_duration DESC) AS rank
FROM EmployeeProjectDuration) 
SELECT 
employee_id, employee_name, avg_project_duration
from 
Ranked_Employee
WHERE rank <= 5;

employee_id	employee_name	avg_project_duration
1008	Mary Jil	212
1007	Drew J	150
1001	Jake B	90
1005	David Wick	90

(8 rows affected)

(5 rows affected)

(10 rows affected)

(4 rows affected)