I am trying to create a query for getting the current streak in MySQL based on status

In the above table current streak should be 2( i.e 2022-03-20 - 2022-03-19) based on status 1. Any help or ideas would be greatly appreciated!

CodePudding user response：

WITH cte AS (
    SELECT SUM(Status) OVER (ORDER BY Dated DESC) s1,
           SUM(NOT Status) OVER (ORDER BY Dated DESC) s2
    FROM table
)
SELECT MAX(s1)
FROM cte
WHERE NOT s2;

SELECT DATEDIFF(MAX(CASE WHEN Status THEN Dated END), 
                MAX(CASE WHEN NOT Status THEN Dated END))
FROM table

and so on...

CodePudding user response：

This is a gaps and islands problem. In your case, you want the island of status 1 records which occurs last. We can use the difference in row numbers method, assuming you are using MySQL 8 .

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Dated) rn1,
              ROW_NUMBER() OVER (PARTITION BY Status ORDER BY Dated) rn2
    FROM yourTable
),
cte2 AS (
    SELECT *, RANK() OVER (ORDER BY rn1 - rn2 DESC) rnk
    FROM cte
    WHERE Status = 1
)

SELECT ID, Dated, Status
FROM cte2
WHERE rnk = 1
ORDER BY Dated;

Demo

CodePudding user response：

We can use 2 one row CTE's to find the latest date where the status was not the same as the latest one and then count the records superieur.

**Schema (MySQL v8.0)**

    create table t(
      ID int,
      Dated date,
      Status int);
    insert into t values
    (1,'2022-03-08',1),
    (2,'2022-03-09',1),
    (3,'2022-03-10',0),
    (4,'2022-03-11',1),
    (5,'2022-03-12',0),
    (6,'2022-03-13',1),
    (7,'2022-03-14',1),
    (8,'2022-03-16',1),
    (9,'2022-03-18',0),
    (10,'2022-03-19',1),
    (11,'2022-03-20',1);

---

**Query #1**

    with latest AS
    (SELECT
        dated lastDate,
        status lastStatus
     from t
     order by dated desc
     limit 1 ),
     lastDiff as
     (select MAX(dated) diffDate
     from t,latest
     where not status = lastStatus
     )
    select count(*) 
    from t ,lastDiff
    where dated > diffDate;

| count(*) |
| -------- |
| 2        |

---

[View on DB Fiddle](https://www.db-fiddle.com/)

We could also consider using datediff() to find the number of days that the streak has lasted which might be more interesting than count() seeing as there are some days where there is no record.