SQL Interview Question: Identify Employees with High Sick Leave and Low Attendance Rate

In this SQL scenario-based interview question, we’ll demonstrate how to find employees who have taken more than 5 days of sick leave in the last 6 months and have an attendance rate of less than 80%. Using SQL aggregation functions like COUNT(), SUM(), and window functions, we’ll solve a common business problem of identifying employees with attendance issues. This scenario is useful for HR analysts and anyone working with employee performance data.

#sqlserver
#sqlquery
#whatissql
#sqldatabase
#oracle
#oraclesql
#SQLtutorialforbeginners
#learnSQLfromscratch
#SQLfromzerotohero
#SQLbasicstoadvanced
#SQLqueries
#SQLfordataanalysis
#SQLjoinsexplained
#SQLsubqueriestutorial
#SQLaggregationfunctions
#SQLinterviewquestions
#SQLrealworldexamples
#SQLmasterclass
#SQLtutorial2024
#learnSQLfast
#SQLfordatascience
#SQLbeginnertoexpert

drop table if exists Employee
drop table if exists Attendance

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT
);
CREATE TABLE Attendance (
AttendanceID INT PRIMARY KEY,
EmployeeID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
Date DATE,
AttendanceStatus VARCHAR(10) -- 'Sick', 'Present', or 'Absent'
);
select * from Employee
select * from Attendance
INSERT INTO Employee (EmployeeID, EmployeeName, DepartmentID)
VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 103),
(4, 'David', 104),
(5, 'Eve', 105);
INSERT INTO Attendance (AttendanceID, EmployeeID, Date, AttendanceStatus)
VALUES
(1, 1, '2024-06-01', 'Present'),
(2, 1, '2024-06-02', 'Sick'),
(3, 1, '2024-06-03', 'Present'),
(4, 1, '2024-06-04', 'Sick'),
(5, 1, '2024-06-05', 'Sick'),
(6, 1, '2024-06-06', 'Sick'),
(7, 1, '2024-06-07', 'Sick'),
(8, 1, '2024-06-08', 'Present'),

(9, 2, '2024-06-01', 'Absent'),
(10, 2, '2024-06-02', 'Present'),
(11, 2, '2024-06-03', 'Sick'),
(12, 2, '2024-06-04', 'Present'),
(13, 2, '2024-06-05', 'Present'),

(14, 3, '2024-06-01', 'Sick'),
(15, 3, '2024-06-02', 'Sick'),
(16, 3, '2024-06-03', 'Sick'),
(17, 3, '2024-06-04', 'Present'),
(18, 3, '2024-06-05', 'Present'),

(19, 4, '2024-06-01', 'Present'),
(20, 4, '2024-06-02', 'Absent'),
(21, 4, '2024-06-03', 'Absent'),
(22, 4, '2024-06-04', 'Absent'),
(23, 4, '2024-06-05', 'Absent'),

(24, 5, '2024-06-01', 'Sick'),
(25, 5, '2024-06-02', 'Present'),
(26, 5, '2024-06-03', 'Sick'),
(27, 5, '2024-06-04', 'Sick'),
(28, 5, '2024-06-05', 'Sick'),
(29, 5, '2024-06-06', 'Present');