SQLize Online / PHPize Online  /  SQLtest Online

WITH RECURSIVE t1(v, o) AS ( SELECT rental_date, return_date FROM rental WHERE customer_id = 1 ), t2(d, c) AS ( SELECT v, 1 FROM t1 UNION SELECT o, -1 FROM t1 ORDER BY 1 ), t3(n, d, c) AS ( SELECT ROW_NUMBER() OVER (ORDER BY d), d, c FROM t2 ), t4(n, t) AS ( SELECT 0, 0 UNION SELECT t4.n + 1, t4.t + t3.c FROM t4 JOIN t3 ON t4.n + 1 = t3.n ), t5(n, t, s) AS ( SELECT *, IF(LAG(t) OVER(ORDER BY n) = 0, 1, 0) FROM t4 ), t6 AS ( SELECT * FROM t5 WHERE n > 0 AND (t = 0 OR s = 1) ), t7(n, e) AS ( SELECT n, IF(s = 1, LAG(n) OVER(ORDER BY n), NULL) FROM t6 ) SELECT * FROM t7