First posts with specific tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Movies and TV

Q&A for movie and tv enthusiasts

DECLARE @Pattern VARCHAR(25) = ##TagPattern:string?%identify-this-%##;

WITH FirstPosts AS
(
  SELECT P.*, 
    (CASE WHEN P.PostTypeId = 1 THEN 1 ELSE 0 END) AS [IsQuestion], 
    (CASE WHEN (CASE WHEN P.PostTypeId = 1 THEN P.Tags ELSE 
      (SELECT Q.Tags FROM Posts Q WHERE P.ParentId = Q.Id) END) LIKE @Pattern 
     THEN 1 ELSE 0 END) AS [IsId]
  FROM Users U INNER JOIN Posts P ON 
    P.OwnerUserId = U.Id AND (P.PostTypeId = 1 OR P.PostTypeId = 2) AND
    NOT EXISTS (SELECT * FROM Posts Q WHERE Q.OwnerUserId = P.OwnerUserId AND 
      (Q.PostTypeId = 1 OR Q.PostTypeId = 2) AND Q.CreationDate < P.CreationDate)
)

SELECT count(*) AS [All], 
  sum(IsQuestion) AS [Questions], 
  sum(1-IsQuestion) AS [Answers], 
  sum(IsID) AS [ID], 
  sum(1-IsID) AS [Non-ID], 
  sum(IsQuestion*IsId) AS [ ID Questions], 
  sum((1-IsQuestion)*IsId) AS [ID Answers], 
  sum(IsQuestion*(1-IsID)) AS [Non-ID Questions], 
  sum((1-IsQuestion)*(1-IsID)) AS [Non-ID Answers]
FROM FirstPosts

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results