Ando Ando - 3 months ago 19
MySQL Question

count consecutive days (streak) and number of records for current day

The following code was taken from another question on SO. Original Q&A

I would like to count the number of consecutive days (Streak) with records since today AND also how many records were made today. I'm using this to send notifications. If a user submits a new record the same day, they should not get a second notification telling them that they are on a streak (they were made aware the first time they submitted a record for the current day).

I tried adding a

COUNT()
function before
@streak
, after the first
SELECT
and pretty much everywhere that seemed reasonable but this query is too complex for me to figure it out.

SELECT streak + 1 as realStreak
FROM (
SELECT dt,
@streak := @streak+1 streak,
datediff(curdate(),dt) diff
FROM (
SELECT distinct date(dt) dt
FROM glucose where uid = 1
) t1
CROSS JOIN (SELECT @streak := -1) t2
ORDER BY dt desc
)
t1 where streak = diff
ORDER BY streak DESC LIMIT 1


http://sqlfiddle.com/#!9/45d386/1/0

The result of the above should be:

realStreak | RecordsToday
3 | 3

Answer

Just add a subquery for the today check

SELECT streak + 1 as realStreak,cdt
FROM (
SELECT dt,
@streak := @streak+1 streak, 
datediff(curdate(),dt) diff
FROM (
SELECT distinct date(dt) dt
FROM gl where uid = 1
) t1
CROSS JOIN (SELECT @streak := -1) t2
ORDER BY dt desc
)t1
JOIN
(SELECT COUNT(CASE WHEN DATE(dt)=CURDATE() THEN 1 END) cdt FROM gl)x
where streak = diff
ORDER BY streak DESC LIMIT 1
Comments