Notes

-- Identifying the Location with the Most Employees:
-- Query to count the number of employees in each region and identify the region with the highest
-- number of employees.

SELECT region, COUNT(*) AS employee_count
FROM `dataset.employee_table`
GROUP BY region
ORDER BY employee_count DESC
LIMIT 1;

-- -------------------------------------------------------------------------------------

-- Query to calculate the average salary for each employee role.

SELECT role, AVG(salary) AS average_salary
FROM `dataset.employee_table`
GROUP BY role;
---------------------------------------------------------------------------------------

-- Identifying Employees in Bench (Not Assigned to a Project):


SELECT e.name AS employee_name, e.employee_id
FROM `dataset.employee_table` AS e
LEFT JOIN `dataset.project_assignment_table` AS p
ON e.employee_id = p.employee_id
WHERE p.project_id IS NULL;

-- -------------------------------------------------------------------------------------------

-- Query to find employees who have taken leaves more than three times in the current month.

SELECT employee_name, COUNT(*) AS leave_count
FROM `dataset.leave_table`
WHERE DATE_TRUNC(leave_date, MONTH) = DATE_TRUNC(CURRENT_DATE(), MONTH)
GROUP BY employee_name
HAVING COUNT(*) > 3;

-- ---------------------------------------------------------------------------------------------

-- Query to count the number of employees assigned to each project in each region.



SELECT region, project_name, COUNT(*) AS employee_count
FROM `dataset.employee_table` AS e
JOIN `dataset.project_assignment_table` AS p
ON e.employee_id = p.employee_id
GROUP BY region, project_name;

-- ------------------------------------------------------------------------------------------------------

-- Identifying Employees with High Utilization Rate:
-- Query to find employees who have spent the most time on projects compared to their scheduled work hours.

SELECT e.name AS employee_name, SUM(p.actual_hours) AS total_project_hours,
e.scheduled_hours, (SUM(p.actual_hours) / e.scheduled_hours) AS utilization_rate
FROM `dataset.employee_table` AS e
JOIN `dataset.project_assignment_table` AS p
ON e.employee_id = p.employee_id
GROUP BY e.name, e.scheduled_hours
ORDER BY utilization_rate DESC
LIMIT 10;

-- ----------------------------------------------------------------------------------------------

-- Query to count the number of employees with each skill in each region.

SELECT region, skill, COUNT(*) AS employee_count
FROM `dataset.employee_table` AS e
JOIN `dataset.employee_skills_table` AS es
ON e.employee_id = es.employee_id
GROUP BY region, skill;

-- -------------------------------------------------------------------------------------------------
-- Identifying Overdue Projects:
-- Query to find projects that are past their due date along with the number of overdue days.


SELECT project_name, due_date, CURRENT_DATE() AS current_date,
DATEDIFF(CURRENT_DATE(), due_date) AS overdue_days
FROM `dataset.project_table`
WHERE CURRENT_DATE() > due_date;
-- ---------------------------------------------------------------------------------------------------

-- Tracking Employee Performance Over Time:
-- Query to calculate the average rating given to each employee over the past six months.

SELECT employee_id, AVG(rating) AS average_rating
FROM `dataset.performance_reviews_table`
WHERE review_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
GROUP BY employee_id;

-- --------------------------------------------------------------------------------------------------

-- Query to calculate the tenure (in years) of each employee in the organization.



SELECT name AS employee_name, DATE_DIFF(CURRENT_DATE(), hire_date, YEAR) AS tenure_years
FROM `dataset.employee_table`;

-- -------------------------------------------------------------------------------------------------

-- identifying employees involved in active projects along with their project details and respective managers:



SELECT e.name AS employee_name, e.region, p.project_name, p.start_date, p.end_date, m.name AS manager_name
FROM `dataset.employee_table` AS e
JOIN `dataset.project_assignment_table` AS pa ON e.employee_id = pa.employee_id
JOIN `dataset.project_table` AS p ON pa.project_id = p.project_id
JOIN `dataset.employee_table` AS m ON p.manager_id = m.employee_id;

-- -------------------------------------------------------------------------------------------------
-- a query that joins multiple tables to identify employees who is on the bench
-- and joined 6 months ago:

SELECT e.name AS employee_name, e.employee_id, e.department, e.designation
FROM `dataset.employee_table` AS e
LEFT JOIN `dataset.project_assignment_table` AS pa ON e.employee_id = pa.employee_id
LEFT JOIN `dataset.project_table` AS p ON pa.project_id = p.project_id
WHERE pa.project_id IS NULL
AND e.hire_date <= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);

-- -----------------------------------------------------------------------------------------------

-- a query to identify the top five locations with the maximum number of employees on the bench along with their respective departments:

SELECT e.region, e.department, COUNT(*) AS employees_on_bench
FROM `dataset.employee_table` AS e
LEFT JOIN `dataset.project_assignment_table` AS pa ON e.employee_id = pa.employee_id
WHERE pa.project_id IS NULL
GROUP BY e.region, e.department
ORDER BY employees_on_bench DESC
LIMIT 5;

-- --------------------------------------------------------------------------------------------------------
-- names of employees from IT Department who haven't been late for a year, assuming 9:00 am is the login time for them

SELECT e.name AS employee_name, e.designation, e.reporting_manager, e.location
FROM `dataset.employee_table` AS e
LEFT JOIN `dataset.employee_attendance_table` AS a ON e.employee_id = a.employee_id
GROUP BY e.name, e.designation, e.reporting_manager, e.location
HAVING MAX(TIME(a.arrival_time)) < '09:00:00' OR MAX(a.arrival_time) IS NULL;
-- --------------------------------------------------------------------------------------------
-- find the number of people working on a particular project and group them by department, you can use the following query:

SELECT e.department, COUNT(*) AS number_of_people
FROM `dataset.employee_table` AS e
JOIN `dataset.project_assignment_table` AS pa ON e.employee_id = pa.employee_id
WHERE pa.project_id = 'your_project_id'
GROUP BY e.department;


-- -------------------------------------------------------------------------------------------

-- STORED PROCEDURES --

-- -------------------------------------------------------------------------------------------------------
-- create a stored procedure to calculate the late entries of employees
CREATE OR REPLACE PROCEDURE calculate_late_entries()
BEGIN
-- first i am creating a table to store the late entries
CREATE TABLE IF NOT EXISTS late_entries (
employee_name STRING,
late_date DATE
);

--NOw Insert late entries into the late_entries table
INSERT INTO late_entries (employee_name, late_date)
SELECT
CONCAT(first_name, ' ', last_name) AS employee_name,
entry_date AS late_date
FROM
employees e
JOIN
entries en ON e.employee_id = en.employee_id
WHERE
entry_date > expected_entry_time; -- i am assuming expected_entry_time is a column in the entries table

END;

-- now callaing the sp which i have created above

CALL calculate_late_entries();
-------------------------------------------------------------------------------------------------------

-- create a stored procedure to calculate the total leaves taken by employees
CREATE OR REPLACE PROCEDURE calculate_total_leaves()
BEGIN
-- Create a table to store total leaves taken by employees
CREATE TABLE IF NOT EXISTS total_leaves (
employee_name STRING,
total_leaves_taken INT64
);

-- Insert total leaves taken by employees into the total_leaves table
INSERT INTO total_leaves (employee_name, total_leaves_taken)
SELECT
CONCAT(first_name, ' ', last_name) AS employee_name,
COUNT(leaves.leave_date) AS total_leaves_taken
FROM
employees e
LEFT JOIN
leaves ON e.employee_id = leaves.employee_id
GROUP BY
employee_name;

END;

-- now calling the sp which i have created above

CALL calculate_total_leaves();
--------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE get_employees_absent_this_month(


IN threshold INT64, -- Input parameter: Threshold for number of absences
OUT employee_ids ARRAY<STRING> -- Output parameter: IDs of employees absent more than the threshold
)
BEGIN
-- Declare variables for current month start and end dates
DECLARE start_date DATE;
DECLARE end_date DATE;

-- Calculate current month start and end dates
SET start_date = DATE_TRUNC(CURRENT_DATE(), MONTH);
SET end_date = DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH);



-- Get IDs of employees absent more than the threshold this month
SET employee_ids = (
SELECT ARRAY_AGG(employee_id)
FROM (
SELECT
employee_id,
COUNT(*) AS num_absences
FROM
absences
WHERE
absence_date BETWEEN start_date AND end_date
GROUP BY
employee_id
HAVING
num_absences > threshold
)
);
END;


DECLARE employee_ids ARRAY<STRING>;

CALL get_employees_absent_this_month(5, employee_ids);