By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE status
(`Date` varchar(10), `isDone` int)
;
INSERT INTO status
(`Date`, `isDone`)
VALUES
('2018-10-01', 1),
('2018-10-02', 1),
('2018-10-03', 1),
('2018-10-04', 1),
('2018-10-10', 0),
('2018-10-15', 1),
('2018-10-16', 0),
('2018-10-18', 1),
('2018-10-19', 1),
('2018-10-20', 1)
;
Records: 10 Duplicates: 0 Warnings: 0
SELECT s.Date,
@streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
@last_date := Date AS last_date
FROM status s
JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
ORDER BY s.Date
| Date | streak | last_date |
|---|---|---|
| 2018-10-01 | 1 | 2018-10-01 |
| 2018-10-02 | 2 | 2018-10-02 |
| 2018-10-03 | 3 | 2018-10-03 |
| 2018-10-04 | 4 | 2018-10-04 |
| 2018-10-10 | 1 | 2018-10-10 |
| 2018-10-15 | 1 | 2018-10-15 |
| 2018-10-16 | 1 | 2018-10-16 |
| 2018-10-18 | 1 | 2018-10-18 |
| 2018-10-19 | 2 | 2018-10-19 |
| 2018-10-20 | 3 | 2018-10-20 |
SELECT Date, streak
FROM (SELECT s.Date,
@streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
@last_date := Date AS last_date
FROM status s
JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
ORDER BY s.Date) s
WHERE s.Date = '2018-10-20'
| Date | streak |
|---|---|
| 2018-10-20 | 3 |