Employee Absences

Problem

Table: Employee

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| employee_id    | int     |
| employee_name  | varchar |
| start_date     | date    |
| end_date       | date    |
+----------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the employee's name and the start and end dates of their absence period.
It is guaranteed that start_date <= end_date.

Problem Definition

Write a solution to find all employees who were absent for more than three consecutive days.

Return the result table ordered by employee_name in ascending order.

Example

Input: 
Employee table:
+-------------+---------------+------------+------------+
| employee_id | employee_name | start_date | end_date   |
+-------------+---------------+------------+------------+
| 1           | Alice         | 2020-02-01 | 2020-02-04 |
| 2           | Bob           | 2020-02-05 | 2020-02-09 |
| 3           | Charlie       | 2020-02-10 | 2020-02-11 |
| 4           | David         | 2020-02-15 | 2020-02-20 |
| 5           | Eve           | 2020-02-25 | 2020-02-28 |
+-------------+---------------+------------+------------+

Output: 
+---------------+------------+------------+
| employee_name | start_date | end_date   |
+---------------+------------+------------+
| Alice         | 2020-02-01 | 2020-02-04 |
| Bob           | 2020-02-05 | 2020-02-09 |
| David         | 2020-02-15 | 2020-02-20 |
+---------------+------------+------------+

Try It Yourself

-- TODO: Write your user queries here

Solution

To solve this problem, the approach involves using SQL queries to analyze the Employee table and identify employees who were absent for more than three consecutive days. The table contains information about employee absences, including their names, start dates, and end dates of absence periods.

The solution employs a WHERE clause to filter the records based on the condition that the difference between the end_date and the start_date is greater than three days. This ensures that only employees who were absent for more than three consecutive days are included in the result.

The final step involves ordering the result table by employee_name in ascending order, as specified in the problem statement.

SELECT employee_name,
       start_date,
       end_date
FROM   Employee
WHERE  DATEDIFF(end_date, start_date) >= 3
ORDER  BY employee_name ASC;

Let's break down the query step by step:

Step 1: Filtering Absences Longer Than Three Days

We apply a condition to select only those absences where the duration is more than three days.

WHERE DATEDIFF(end_date, start_date) >= 3

• >= 3 Condition: This ensures that employees with exactly 4 days of absence (like Alice) are included, along with those who were absent for more than 3 days.
• • The DATEDIFF function calculates the difference in days as: DATEDIFF('2020-02-04', '2020-02-01') = 3. So, >=3 condition is neccessary.

Step 2: Ordering the Result by Employee Name

Finally, we order the results by employee_name in ascending order to align with the problem statement.

ORDER BY employee_name ASC

Final Output:

+---------------+------------+------------+
| employee_name | start_date | end_date   |
+---------------+------------+------------+
| Alice         | 2020-02-01 | 2020-02-04 |
| Bob           | 2020-02-05 | 2020-02-09 |
| David         | 2020-02-15 | 2020-02-20 |
+---------------+------------+------------+

This final result table lists the employees who were absent for more than three consecutive days, sorted by their names.

I just read a lesson on **Employee Absences** (Databases) and want to truly understand it. Explain Employee Absences from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Employee Absences** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Employee Absences** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Employee Absences** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.