Q&A about the site for people organizing, marketing or licensing open source development projects
WITH groups(userid, date, grp) AS ( SELECT OwnerUserId, CAST(CreationDate AS DATE), dateadd(day, -DENSE_RANK() OVER (ORDER BY CAST(CreationDate AS DATE)), CAST(CreationDate AS DATE)) AS grp FROM Posts where OwnerUserid = ##UserId## ) SELECT COUNT(*) AS consecutiveDates, MIN(date) AS minDate, MAX(date) AS maxDate FROM groups GROUP BY userid, grp ORDER BY userid, consecutiveDates DESC, minDate DESC;