Q&A for movie and tv enthusiasts
DECLARE @StartTime DATETIME = convert(DATETIME, '##StartTime?2012/01/01 00:00:00##') DECLARE @Pattern VARCHAR(25) = ##TagPattern:string?%identify-this-%##; WITH UserQuestions AS ( SELECT U.*, Q.Tags, Q.PostTypeId, (CASE WHEN EXISTS (SELECT * FROM Posts P WHERE P.OwnerUserId = U.Id AND P.Id <> Q.Id) THEN 1 ELSE 0 END) AS [HasMore], (CASE WHEN EXISTS (SELECT * FROM Posts P WHERE P.OwnerUserId = U.Id AND P.Id <> Q.Id AND ((P.PostTypeId = 2 -- AND (SELECT Tags FROM Posts Q WHERE P.ParentId = Q.Id) NOT LIKE @Pattern ) OR P.Tags NOT LIKE @Pattern)) THEN 1 ELSE 0 END) AS [HasNonID], (SELECT count(*) FROM Posts P WHERE P.OwnerUserId = U.Id AND P.PostTypeId = 1) AS [Questions], (SELECT count(*) FROM Posts P WHERE P.OwnerUserId = U.Id AND P.PostTypeId = 2) AS [Answers], (SELECT count(*) FROM Posts P WHERE P.OwnerUserId = U.Id AND P.Id <> Q.Id AND ((P.PostTypeId = 2 -- AND (SELECT Tags FROM Posts Q WHERE P.ParentId = Q.Id) NOT LIKE @Pattern ) OR P.Tags NOT LIKE @Pattern)) AS [NonID] FROM Users U INNER JOIN Posts Q ON Q.PostTypeId = 1 AND Q.OwnerUserId = U.Id AND NOT EXISTS (SELECT * FROM Posts P WHERE P.OwnerUserId = Q.OwnerUserId AND P.PostTypeId = 1 AND P.CreationDate < Q.CreationDate) WHERE U.CreationDate > @StartTime ) SELECT 'All Askers' AS [Users], count(*) AS [#], sum(HasMore) AS [With more posts], sum(HasNonID) AS [With non-ID posts], round(avg(cast(Questions AS FLOAT)), 2) AS [Questions], round(avg(cast(Answers AS FLOAT)), 2) AS [Answers], round(avg(cast(NonID AS FLOAT)), 2) AS [Non-ID Posts], round(avg(cast(Reputation AS FLOAT)), 0) AS [Reputation] FROM UserQuestions UNION SELECT 'ID Askers' AS [Users], count(*) AS [#], sum(HasMore) AS [With more posts], sum(HasNonID) AS [With non-ID posts], round(avg(cast(Questions AS FLOAT)), 2) AS [Questions], round(avg(cast(Answers AS FLOAT)), 2) AS [Answers], round(avg(cast(NonID AS FLOAT)), 2) AS [Non-ID Posts], round(avg(cast(Reputation AS FLOAT)), 0) AS [Reputation] FROM UserQuestions WHERE Tags LIKE @Pattern UNION SELECT 'Non-ID Askers' AS [Users], count(*) AS [#], sum(HasMore) AS [With more posts], sum(HasNonID) AS [With non-ID posts], round(avg(cast(Questions AS FLOAT)), 2) AS [Questions], round(avg(cast(Answers AS FLOAT)), 2) AS [Answers], round(avg(cast(NonID AS FLOAT)), 2) AS [Non-ID Posts], round(avg(cast(Reputation AS FLOAT)), 0) AS [Reputation] FROM UserQuestions WHERE PostTypeId = 2 OR Tags NOT LIKE @Pattern