Overall statistics of 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 *, 
    (SELECT sum(A.Score) FROM Posts A WHERE A.ParentId = Q.Id) AS [AnswerScore], 
    (CASE WHEN Q.AcceptedAnswerId IS NOT NULL OR EXISTS 
      (SELECT * FROM Posts A WHERE A.ParentId = Q.Id AND A.Score > 0) 
     THEN 1 ELSE 0 END) AS [Answered]
  FROM
    Posts AS Q
  WHERE
    Q.PostTypeId = 1
)

SELECT
  'All' AS [Questions], 
  round(avg(cast(ViewCount AS FLOAT)), 0) AS [Views], 
  round(avg(cast(Score AS FLOAT)), 2) AS [Score], 
  round(avg(cast(AnswerCount AS FLOAT)), 2) AS [Answers], 
  round(avg(cast(AnswerScore AS FLOAT)/cast(AnswerCount AS float)), 2) AS [Answer Score],  
  round(100.0 * sum(Answered) / count(*), 1) AS [Answered %], 
  round(100.0 * count(AcceptedAnswerId) / count(*), 1) AS [Accepted %]
FROM Questions

UNION

SELECT
  'ID' AS [Questions], 
  round(avg(cast(ViewCount AS FLOAT)), 0) AS [Views], 
  round(avg(cast(Score AS FLOAT)), 2) AS [Score], 
  round(avg(cast(AnswerCount AS FLOAT)), 2) AS [Answers], 
  round(avg(cast(AnswerScore AS FLOAT)/cast(AnswerCount AS float)), 2) AS [Answer Score],  
  round(100.0 * sum(Answered) / count(*), 1) AS [Answered %], 
  round(100.0 * count(AcceptedAnswerId) / count(*), 1) AS [Accepted %]
FROM Questions
WHERE Tags LIKE @Pattern

UNION

SELECT
  'Non-ID' AS [Questions], 
  round(avg(cast(ViewCount AS FLOAT)), 0) AS [Views], 
  round(avg(cast(Score AS FLOAT)), 2) AS [Score], 
  round(avg(cast(AnswerCount AS FLOAT)), 2) AS [Answers], 
  round(avg(cast(AnswerScore AS FLOAT)/cast(AnswerCount AS float)), 2) AS [Answer Score],  
  round(100.0 * sum(Answered) / count(*), 1) AS [Answered %], 
  round(100.0 * count(AcceptedAnswerId) / count(*), 1) AS [Accepted %]
FROM Questions
WHERE Tags NOT LIKE @Pattern
/*
UNION

SELECT
  'All stdev' AS [Questions], 
  round(stdevp(cast(ViewCount AS FLOAT)), 0) AS [Views], 
  round(stdevp(cast(Score AS FLOAT)), 2) AS [Score], 
  round(stdevp(cast(AnswerCount AS FLOAT)), 2) AS [Answers], 
  round(stdevp(cast(AnswerScore AS FLOAT)/cast(AnswerCount AS float)), 2) AS [Answer Score],  
  NULL AS [Answered %], 
  NULL AS [Accepted %]
FROM Questions

UNION

SELECT
  'ID stdev' AS [Questions], 
  round(stdevp(cast(ViewCount AS FLOAT)), 0) AS [Views], 
  round(stdevp(cast(Score AS FLOAT)), 2) AS [Score], 
  round(stdevp(cast(AnswerCount AS FLOAT)), 2) AS [Answers], 
  round(stdevp(cast(AnswerScore AS FLOAT)/cast(AnswerCount AS float)), 2) AS [Answer Score],  
  NULL AS [Answered %], 
  NULL AS [Accepted %]
FROM Questions
WHERE Tags LIKE @Pattern

UNION

SELECT
  'Non-ID stdev' AS [Questions], 
  round(stdevp(cast(ViewCount AS FLOAT)), 0) AS [Views], 
  round(stdevp(cast(Score AS FLOAT)), 2) AS [Score], 
  round(stdevp(cast(AnswerCount AS FLOAT)), 2) AS [Answers], 
  round(stdevp(cast(AnswerScore AS FLOAT)/cast(AnswerCount AS float)), 2) AS [Answer Score],  
  NULL AS [Answered %], 
  NULL AS [Accepted %]
FROM Questions
WHERE Tags NOT LIKE @Pattern

Enter Parameters

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