Aggregate 2 vacation periods into one if they are separated by the same weekend in Mysql

Your current query does exactly what you have asked of it. It extends the end_date of the CURRENT ROW to that of the LEAD ROW when the conditions are met:

One approach to this problem is to treat it as a gaps-and-islands problem, as you are looking for sequences of consecutive free days.

We start by creating a list of all dates we are interested in:

WITH RECURSIVE calendar (dt) AS (
    SELECT MIN(start_date) FROM vacations
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM calendar WHERE dt < (SELECT MAX(end_date) FROM vacations)
)

Note: cte_max_recursion_depth has a default value of 1000, causing the CTE to terminate when it recurses past 1000 levels. You can read more here – Limiting Common Table Expression Recursion.

If you are running this query for a period greater than 1000 days you will need to increase the recursion depth:

SET SESSION cte_max_recursion_depth = 10000;

We can then CROSS JOIN the calendar to a distinct list of the employees we are interested in (I have used a DISTINCT query on the vacations table), and then LEFT JOIN to vacations to get a full list of when employees have a free_day, either because they are on vacation or it is a weekend day (v.id IS NOT NULL OR WEEKDAY(c.dt) IN (5, 6)).

To look for sequences of consecutive free-days we are using c.dt - INTERVAL (DENSE_RANK() OVER (PARTITION BY e.marca ORDER BY c.dt)) DAY which can be illustrated by:

ROW_NUMBER() is commonly used for the above grouping but, due to your example having an employee with two vacation entries for the same date (750003, 2022-08-23), we need to use DENSE_RANK() instead.

WITH RECURSIVE calendar (dt) AS (
    SELECT MIN(start_date) FROM vacations
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM calendar WHERE dt < (SELECT MAX(end_date) FROM vacations)
),
free_days AS (
    SELECT c.dt, e.marca, v.start_date, v.end_date,
        c.dt - INTERVAL (DENSE_RANK() OVER (PARTITION BY e.marca ORDER BY c.dt)) DAY AS free_day_grp
    FROM calendar c
    CROSS JOIN (SELECT DISTINCT marca FROM vacations) e
    LEFT JOIN vacations v
        ON c.dt BETWEEN v.start_date AND v.end_date
        AND e.marca = v.marca
    WHERE v.id IS NOT NULL OR WEEKDAY(c.dt) IN (5, 6)
)
SELECT marca, MIN(start_date) AS start_date, MAX(end_date) AS end_date
FROM free_days
GROUP BY marca, free_day_grp
HAVING DATEDIFF(end_date, start_date) + 1 > 10;

-- Obviously, the HAVING clause could be written as
-- HAVING DATEDIFF(end_date, start_date) > 9;

Output:

Here’s a db<>fiddle