The Employee That Worked On The Longest Task - Leetcode Solution

Problem Statement: The problem "Employee That Worked On The Longest Task" is a Leetcode problem. The problem statement is as follows:

"Table: Tasks +---------------+---------+ | Column Name | Type | +---------------+---------+ | task_id | int | | employee_id | int | | start_date | date | | end_date | date | +---------------+---------+ (task_id, employee_id) is the primary key of this table. Each row of this table indicates that the employee with ID employee_id has worked on the task with ID task_id starting from the date start_date and ending on the date end_date. Note that the end_date of a previous task can be the same as the start_date of the following task.

Table: Employees +---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | employee_name | varchar | +---------------+---------+ employee_id is the primary key of this table.

Write an SQL query that reports the employee_name and the total duration (in days) worked for the longest duration task of each employee. If there is more than one task with the longest duration, report all of them.

The query result format is in the following example:

Tasks table: +---------+-------------+------------+------------+ | task_id | employee_id | start_date | end_date | +---------+-------------+------------+------------+ | 1 | 1 | 2020-01-01 | 2020-01-02 | | 2 | 1 | 2020-01-02 | 2020-01-03 | | 3 | 2 | 2020-01-01 | 2020-01-02 | | 4 | 2 | 2020-01-02 | 2020-01-03 | | 5 | 2 | 2020-01-03 | 2020-01-04 | | 6 | 3 | 2020-01-01 | 2020-01-01 | | 7 | 3 | 2020-01-02 | 2020-01-03 | +---------+-------------+------------+------------+

Employees table: +-------------+---------------+ | employee_id | employee_name | +-------------+---------------+ | 1 | Alice | | 2 | Bob | | 3 | Charlie | +-------------+---------------+

Result table: +---------------+---------------+----------------+ | employee_name | task_id | total_duration | +---------------+---------------+----------------+ | Alice | 1 | 2 | | Alice | 2 | 2 | | Bob | 3 | 2 | | Bob | 4 | 2 | | Bob | 5 | 1 | | Charlie | 7 | 2 | +---------------+---------------+----------------+

Alice has two tasks with the largest duration: tasks 1 and 2 with a duration of 2 days each. Bob has three tasks with the largest duration: tasks 3, 4, and 5 with a duration of 2, 2, and 1 day respectively. Charlie has only one task with the largest duration: task 7 with a duration of 2 days.

1