Q&A about the site for people organizing, marketing or licensing open source development projects
WITH -- This table contains all the distinct date -- instances in the data set dates(date) AS ( SELECT DISTINCT CAST(CreationDate AS DATE) FROM Posts WHERE OwnerUserId = ##UserId## ), -- Generate "groups" of dates by subtracting the -- date's row number (no gaps) from the date itself -- (with potential gaps). Whenever there is a gap, -- there will be a new group groups AS ( SELECT ROW_NUMBER() OVER (ORDER BY date) AS rn, dateadd(day, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp, date FROM dates ) SELECT COUNT(*) AS consecutiveDates, MIN(date) AS minDate, MAX(date) AS maxDate FROM groups GROUP BY grp ORDER BY 1 DESC, 2 DESC