Q&A about the site for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing
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, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY COUNT(*) DESC) AS rank FROM groups GROUP BY userid, grp ) SELECT userid, consecutiveDates AS maxConsecutiveDates, minDate, maxDate FROM max_consecutive_dates --where rank=1 order by 2 Desc, 3 desc;