TodaysHotnessStreak.sql

SELECT currentRank.rank, currentRank.bggid, IFNULL(g.name, currentRank.bggid) AS gameName, historicalRank.RowNumber AS StreakInDays
FROM
(
	SELECT hg.bggid, hg.rank
	FROM hotgame AS hg 
	WHERE DATE(hg.date) = CURDATE()
) AS currentRank
JOIN 
(
	SELECT 
	hg.bggid 
	, hg.rank
	, DATE(hg.date) AS DateCol
	,(@rn:= if((@bggid = bggid) AND (@rank = rank), @rn + 1,
	if((@bggid:= bggid) AND (@rank:= rank), 1, 1)
	)
	) as RowNumber
	FROM 
	hotgame AS hg 
	CROSS JOIN (SELECT @bggid := 0, @rank:=0, @rn:= 0) var
) AS historicalRank ON historicalRank.bggid = currentRank.bggid AND historicalRank.rank = currentRank.rank AND historicalRank.DateCol = curdate()
LEFT JOIN games AS g ON g.bggid = currentRank.bggid
GROUP BY currentRank.bggid, currentRank.rank, g.name
ORDER BY currentRank.rank
;