Q&A for people looking to bypass life's everyday problems with simple tricks
WITH groups(userid, date, grp) AS ( SELECT DISTINCT OwnerUserId, CAST(CreationDate AS DATE), dateadd(day, -DENSE_RANK() OVER (PARTITION BY OwnerUserId ORDER BY CAST(CreationDate AS DATE)), CAST(CreationDate AS DATE)) AS grp FROM Posts where OwnerUserId = ##UserId## ), max_consecutive_dates AS ( SELECT userid, COUNT(*) AS consecutiveDates, MIN(date) AS minDate, MAX(date) AS maxDate FROM groups GROUP BY userid, grp ) SELECT userid, MAX(consecutiveDates) AS maxConsecutiveDates, minDate, maxDate FROM max_consecutive_dates GROUP BY userid, minDate, maxDate order by 2 Desc;