Questions 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 Questions AS
(
  SELECT *, 
    (CASE WHEN Q.Tags LIKE @Pattern THEN 1 ELSE 0 END) AS [IsID], 
    (CASE WHEN EXISTS (SELECT * FROM Posts P WHERE 
      P.OwnerUserId = Q.OwnerUserId AND P.PostTypeId = 1 AND 
      P.CreationDate < Q.CreationDate) THEN 0 ELSE 1 END) AS [IsFirstQuestion], 
    (CASE WHEN EXISTS (SELECT * FROM Posts P WHERE 
      P.OwnerUserId = Q.OwnerUserId AND (P.PostTypeId = 1 OR P.PostTypeId = 2) AND 
      P.CreationDate < Q.CreationDate) THEN 0 ELSE 1 END) AS [IsFirstPost]
  FROM Posts Q
  WHERE Q.PostTypeId = 1 --AND Q.OwnerUserId IS NOT NULL
)

SELECT count(*) AS [All], 
  sum(IsID) AS [ID], 
  sum(IsID * IsFirstQuestion) AS [ID First Question], 
  sum((1-IsID) * IsFirstQuestion) AS [Non-ID First Question], 
  sum(IsID * IsFirstPost) AS [ID First Post], 
  sum((1-IsID) * IsFirstPost) AS [Non-ID First Post]
FROM Questions

Enter Parameters

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