Q&A for developers and researchers interested in open data
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 ), max_minDate AS ( SELECT userid, Max(consecutiveDates) as max_c_Date, MAX(minDate) AS max_minDate FROM max_consecutive_dates GROUP BY userid ) SELECT mc.userid, mc.consecutiveDates AS maxConsecutiveDates, mc.minDate, mc.maxDate FROM max_consecutive_dates mc JOIN max_minDate mm ON mc.userid = mm.userid WHERE mc.minDate = mm.max_minDate and mc.ConsecutiveDates = mm.max_c_date;