Q&A for movie and tv enthusiasts
DECLARE @BeginDate DATETIME = (SELECT min(CreationDate) FROM PostHistory); DECLARE @EndDate DATETIME = (SELECT max(CreationDate) FROM PostHistory); DECLARE @Pattern VARCHAR(25) = ##TagPattern:string?%identify-this-%##; WITH Dates AS ( SELECT @BeginDate AS [DateValue] UNION ALL SELECT DateValue + 1 FROM Dates WHERE DateValue + 1 < @EndDate ) SELECT D.DateValue AS [Date], /* (SELECT count(*) FROM Posts P WHERE P.PostTypeId = 1 AND P.CreationDate < D.DateValue) AS [Total], (SELECT count(*) FROM Posts P WHERE P.Tags LIKE @Pattern AND P.CreationDate < D.DateValue) AS [ID], (SELECT count(*) FROM Posts P WHERE P.Tags NOT LIKE @Pattern AND P.CreationDate < D.DateValue) AS [Non-ID] */ (CASE WHEN EXISTS (SELECT * FROM Posts P WHERE P.PostTypeId = 1 AND P.CreationDate < D.DateValue) THEN 100.0 * (SELECT count(*) FROM Posts P WHERE P.Tags LIKE @Pattern AND P.CreationDate < D.DateValue) / (SELECT count(*) FROM Posts P WHERE P.PostTypeId = 1 AND P.CreationDate < D.DateValue) ELSE 0 END) AS [ID %] FROM Dates AS D ORDER BY D.DateValue ASC OPTION (MAXRECURSION 0)