Overall statistics of questions with specific tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

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
  'SW' 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 '%star-wars%'

UNION

SELECT
  'ST' 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 '%star-trek%'

Enter Parameters

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