Viewcount accepted answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH DATA AS (
  SELECT 
          Q.Id
        , viewcount = CAST(Q.viewcount AS BIGINT)
        , Answerid = A.Id
        , A.score
        , Accepted=CAST((CASE WHEN A.Id=ISNULL(Q.[AcceptedAnswerId],0) THEN 1 ELSE 0 END) AS BIT)
  FROM [Posts] A
  INNER JOIN [Posts] Q
  ON A.[parentid]=Q.[id]
  WHERE A.[Posttypeid]=2
)
SELECT 
  [Questions] = (SELECT COUNT(DISTINCT ID) FROM DATA)
 ,[QuestionsAccepted] = (SELECT COUNT(DISTINCT ID) FROM DATA WHERE Accepted=1)
 ,[QuestionsAcceptedViews] = (SELECT SUM(VIEWCOUNT) FROM DATA WHERE Accepted=1)
 ,[QuestionsLowerAccepted] = (SELECT COUNT(DISTINCT ID) FROM DATA D WHERE Accepted=1 AND D.score<(SELECT MAX(D2.score) FROM DATA D2 WHERE D.id=D2.id GROUP BY D2.id))
 ,[QuestionsLowerAcceptedViews] = (SELECT SUM(VIEWCOUNT) FROM DATA D WHERE Accepted=1 AND D.score<(SELECT MAX(D2.score) FROM DATA D2 WHERE D.id=D2.id GROUP BY D2.id))

Enter Parameters

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