https://www.youtube.com/watch?v=F-gETvj-oN0&list=PLavw5C92dz9Hxz0YhttDniNgKejQlPoAn&index=17

STR_TO_DATE()

-- 5 or more consecutive days.
DROP TABLE IF EXISTS user_login;

CREATE TABLE user_login (
    user_id INT,
    login_date DATE
);

INSERT INTO user_login VALUES
(1, STR_TO_DATE('01/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('02/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('03/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('04/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('06/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('10/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('11/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('12/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('13/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('14/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('20/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('25/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('26/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('27/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('28/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('29/03/2024', '%d/%m/%Y')),
(1, STR_TO_DATE('30/03/2024', '%d/%m/%Y')),
(2, STR_TO_DATE('01/03/2024', '%d/%m/%Y')),
(2, STR_TO_DATE('02/03/2024', '%d/%m/%Y')),
(2, STR_TO_DATE('03/03/2024', '%d/%m/%Y')),
(2, STR_TO_DATE('04/03/2024', '%d/%m/%Y')),
(3, STR_TO_DATE('01/03/2024', '%d/%m/%Y')),
(3, STR_TO_DATE('02/03/2024', '%d/%m/%Y')),
(3, STR_TO_DATE('03/03/2024', '%d/%m/%Y')),
(3, STR_TO_DATE('04/03/2024', '%d/%m/%Y')),
(3, STR_TO_DATE('04/03/2024', '%d/%m/%Y')),
(3, STR_TO_DATE('04/03/2024', '%d/%m/%Y')),
(3, STR_TO_DATE('05/03/2024', '%d/%m/%Y')),
(4, STR_TO_DATE('01/03/2024', '%d/%m/%Y')),
(4, STR_TO_DATE('02/03/2024', '%d/%m/%Y')),
(4, STR_TO_DATE('03/03/2024', '%d/%m/%Y')),
(4, STR_TO_DATE('04/03/2024', '%d/%m/%Y')),
(4, STR_TO_DATE('04/03/2024', '%d/%m/%Y'));

SELECT * FROM user_login;

WITH
DENSE_RANK()
DATE_SUB()

WITH cte AS(
SELECT *,dayofmonth(login_date) AS Date,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY dayofmonth(login_date)) AS rn FROM user_login
),
cte2 AS (
SELECT user_id,login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS updated_date FROM cte
)
SELECT user_id,MIN(login_date) AS Start_date,MAX(login_date) AS End_date,(MAX(login_date) - MIN(login_date) + 1)AS Consecutive_days 

FROM cte2
GROUP BY user_id,updated_date 
HAVING Consecutive_days >= 5