0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited aug 22 15

created aug 12 15 Rauy