SQLize Online / PHPize Online  /  SQLtest Online

WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(rental_date, '%Y-%m-%d'), DATE_FORMAT(return_date, '%Y-%m-%d')) AS period_id, DATE_FORMAT(rental_date, '%Y-%m-%d') AS from_date, DATE_FORMAT(return_date, '%Y-%m-%d') AS to_date, LAG(DATE_FORMAT(return_date, '%Y-%m-%d'), 1, '1900-01-01') OVER (ORDER BY DATE_FORMAT(rental_date, '%Y-%m-%d')) AS prev_to_date, SUM(CASE WHEN DATE_FORMAT(rental_date, '%Y-%m-%d') <= LAG(DATE_FORMAT(return_date, '%Y-%m-%d'), 1, '1900-01-01') OVER (ORDER BY DATE_FORMAT(rental_date, '%Y-%m-%d')) THEN 0 ELSE 1 END) OVER (ORDER BY DATE_FORMAT(rental_date, '%Y-%m-%d')) AS group_id FROM rental WHERE customer_id = 1 ) SELECT MIN(from_date) AS from_date, MAX(to_date) AS to_date FROM CTE GROUP BY group_id ORDER BY from_date;