SELECT days
FROM (
SELECT user_id, max(days) days, min(login_day) start_date,max(login_day) end_date
FROM (SELECT user_id,
@cont_day :=
(CASE
WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)=1) THEN
(@cont_day + 1)
WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)<1) THEN
(@cont_day + 0)
ELSE
1
END) AS days,
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_uid := user_id,
@last_dt := login_dt login_day
FROM (SELECT user_id, DATE(create_time) AS login_dt
FROM user_continue_log
ORDER BY user_id, create_time) AS t,
(SELECT @last_uid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1) AS t2
GROUP BY user_id, cont_ix) as tt
where date_format(now(),'%Y-%m-%d') = tt.end_date
and user_id =1
ORDER BY tt.days desc limit 1;
FROM (
SELECT user_id, max(days) days, min(login_day) start_date,max(login_day) end_date
FROM (SELECT user_id,
@cont_day :=
(CASE
WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)=1) THEN
(@cont_day + 1)
WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)<1) THEN
(@cont_day + 0)
ELSE
1
END) AS days,
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_uid := user_id,
@last_dt := login_dt login_day
FROM (SELECT user_id, DATE(create_time) AS login_dt
FROM user_continue_log
ORDER BY user_id, create_time) AS t,
(SELECT @last_uid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1) AS t2
GROUP BY user_id, cont_ix) as tt
where date_format(now(),'%Y-%m-%d') = tt.end_date
and user_id =1
ORDER BY tt.days desc limit 1;