WITH RankedRentals AS (
SELECT
r.rental_date,
r.customer_id,
ROW_NUMBER() OVER(PARTITION BY r.customer_id ORDER BY r.rental_date) AS rn,
DATE(r.rental_date) AS rental_date_only
FROM Rentals AS r
WHERE
r.customer_id = 1
),
GroupedRentals AS (
SELECT
rental_date_only,
customer_id,
rn,
DATE(rental_date_only, '-1 day') AS prev_date,
CASE
WHEN DATE(rental_date_only, '-1 day') = LAG(rental_date_only, 1, NULL) OVER (ORDER BY rn)
THEN 0
ELSE 1
END AS is_new_period
FROM RankedRentals
),
PeriodStarts AS (
SELECT
rental_date_only,
customer_id,
rn,
is_new_period,
SUM(is_new_period) OVER (ORDER BY rn) AS period_id
FROM GroupedRentals
),
PeriodEnds AS (
SELECT
rental_date_only,
customer_id,
rn,
period_id,
DATE(rental_date_only, '+1 day') AS next_date,
CASE
WHEN DATE(rental_date_only, '+1 day') = LEAD(rental_date_only, 1, NULL) OVER (ORDER BY rn)
THEN 0
ELSE 1
END AS is_end_period
FROM PeriodStarts
)
SELECT
period_id,
MIN(rental_date_only) AS from_date,
MAX(rental_date_only) AS to_date
FROM PeriodEnds
WHERE
is_end_period = 1
GROUP BY
period_id
ORDER BY
period_id;
Telegram: DB developers channel