FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

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

CREATE TABLE `work_logs` (
  `employee_id` int DEFAULT NULL,
  `work_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `work_logs` (`employee_id`, `work_date`) VALUES
(1, '2024-07-30'),
(1, '2024-07-22'),
(1, '2024-07-20');

-- SQL Query
WITH ranked_work_logs AS (
    SELECT 
        employee_id,
        work_date,
        ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY work_date) AS rn
    FROM work_logs
),
grouped_work_logs AS (
    SELECT 
        employee_id,
        work_date,
        DATE_SUB(work_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY work_date) DAY) AS grp
    FROM ranked_work_logs
),
islands AS (
    SELECT 
        employee_id,
        MIN(work_date) AS start_date,
        MAX(work_date) AS end_date
    FROM grouped_work_logs
    GROUP BY employee_id, grp
)
SELECT
    a.employee_id,
    a.end_date + INTERVAL 1 DAY AS gap_start,
    b.start_date - INTERVAL 1 DAY AS gap_end,
    DATEDIFF(b.start_date, a.end_date) - 1 AS gap_length
FROM islands a
JOIN islands b
ON a.employee_id = b.employee_id AND a.end_date < b.start_date
ORDER BY a.employee_id, gap_start;

employee_id	gap_start	gap_end	gap_length
1	2024-07-21	2024-07-21	1
1	2024-07-21	2024-07-29	9
1	2024-07-23	2024-07-29	7