Q&A for Drupal developers and administrators
WITH weeks(week) AS ( SELECT DISTINCT datepart(year, CreationDate) * 100 + datepart(week, CreationDate) FROM Posts WHERE OwnerUserId = ##UserId## ), groups AS ( SELECT ROW_NUMBER() OVER (ORDER BY week) AS rn, dateadd(day, -ROW_NUMBER() OVER (ORDER BY week), week) AS grp, week FROM weeks ) SELECT COUNT(*) AS consecutiveWeeks, MIN(week) AS minWeek, MAX(week) AS maxWeek FROM groups GROUP BY grp ORDER BY 1 DESC, 2 DESC