Streakを計算したいときに思い出すSQL。
SELECT MAX(streak) AS streak FROM ( SELECT tmp1.*, IF( @prev + INTERVAL 1 DAY = tmp1.date, @streak := @streak + 1, @streak := 1 ) AS streak, @prev := tmp1.date FROM ( SELECT date FROM logs WHERE user_id = ? group by date order by date asc ) AS tmp1 INNER JOIN (SELECT @prev := NULL, @streak := 1) AS vars ) AS tmp2
MySQL5.7系では動作確認済み。