[PHP] - current streak by date

PHOTO EMBED

Tue May 31 2022 12:14:44 GMT+0000 (Coordinated Universal Time)

Saved by @robert_code 

SELECT MAX(streak) AS streak
FROM (
  SELECT `datetime`,
         DATEDIFF(NOW(), `datetime`),
         @streak := IF( DATEDIFF(NOW(), `datetime`) - @days_diff > 1, @streak, 
                       IF(@days_diff := DATEDIFF(NOW(), `datetime`), @streak+1, @streak+1))  AS streak                                        
  FROM user_session
  CROSS JOIN (SELECT @streak := 0, @days_diff := -1) AS vars
  WHERE user_id = 1 AND `datetime` <= NOW()
  ORDER BY `datetime` DESC) AS t
  
  OR
  
  SELECT MAX(streak) AS streak
FROM (
  SELECT `datetime`,
         DATEDIFF(NOW(), `datetime`),
         @streak := IF( DATEDIFF(NOW(), `datetime`) - @days_diff > 1, @streak, 
                       IF(@days_diff := DATEDIFF(NOW(), `datetime`), @streak+1, @streak+1))  AS streak                                        
  FROM user_session
  CROSS JOIN (SELECT @streak := 0, @days_diff := 1) AS vars
  WHERE user_id = 1 AND `datetime` <= NOW()
  ORDER BY `datetime` DESC) AS t
content_copyCOPY