By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE visitors_data (user_id VARCHAR(7), visit_date DATE);
INSERT INTO visitors_data (user_id, visit_date) VALUES
('murtaza', '2021-01-01'),
('john', '2021-01-01'),
('murtaza', '2021-01-02'),
('murtaza', '2021-01-03'),
('murtaza', '2021-01-04'),
('john', '2021-01-01'),
('murtaza', '2021-01-05'),
('murtaza', '2021-01-06'),
('john', '2021-01-02'),
('john', '2021-01-03'),
('murtaza', '2021-01-07'),
('murtaza', '2021-01-08'),
('murtaza', '2021-01-09'),
('john', '2021-01-20'),
('john', '2021-01-21');
Records: 15 Duplicates: 0 Warnings: 0
SELECT user_id, COALESCE(NULLIF(MAX(counter) % 7, 0), 7) streak
FROM (
SELECT *, COUNT(*) OVER (PARTITION BY user_id, grp ORDER BY visit_date) counter
FROM (
SELECT *, SUM(flag) OVER (PARTITION BY user_id ORDER BY visit_date) grp
FROM (
SELECT *, COALESCE(DATE_ADD(visit_date, INTERVAL -1 DAY) <>
LAG(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date), 1) flag
FROM (SELECT DISTINCT * FROM visitors_data) t
) t
) t
) t
GROUP BY user_id, grp, FLOOR((counter - 1) / 7)
| user_id | streak |
|---|---|
| john | 3 |
| john | 2 |
| murtaza | 7 |
| murtaza | 2 |