This is an automated archive.

The original was posted on /r/mysql by /u/Early-Comb6994 on 2023-08-18 14:48:52+00:00.

Hi all, I was hoping someone could help me?

I need to get amount of days a user has completed a habit in a row (streak).

This means the latest streak (today backwards) and not necessarily their longest streak.

With the following dates inserted, I’m expecting 4

• 2023-09-14
• 2023-09-15
• 2023-09-16
• 2023-09-17

This query is returning 1, where am I going wrong?

SELECT MAX(streak) AS streak

FROM ( SELECT created_at, DATEDIFF(NOW(), created_at), @streak := IF( DATEDIFF(NOW(), created_at) - @days_diff > 1, @streak, IF(@days_diff := DATEDIFF(NOW(), created_at), @streak+1, @streak+1)) AS streak FROM habit_progress_logs CROSS JOIN (SELECT @streak := 0, @days_diff := -1) AS vars WHERE habit_id = 4 AND created_at <= NOW() ORDER BY created_at DESC) AS t

I’ve set up an SQLFiddle here with schema and test data.