SELECT DISTINCT CASE WHEN p1.[AcceptedAnswerId] > 0 THEN ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

SELECT DISTINCT
      CASE WHEN p1.[AcceptedAnswerId] > 0 THEN
          '1'
      ELSE
          '0'
      END                       AS 'Answer_Accepted'
    , p.[Score]                 AS 'Answer_Score'
    , p1.[Score]                AS 'Question_Score'
    , p1.[CommentCount]         AS 'Question_Number_Of_Comments'
    , p1.[ViewCount]            AS 'Question_Number_Of_Views'
    , p1.[Tags]                 AS 'Question_Tags'
    , p.[Body]                  AS 'Answer_Text'
    , p.[CommentCount]          AS 'Answer_Number_Of_Comments'
    , u.[Reputation]            AS 'Author_Reputation'
    , u.[Views]                 AS 'Author_Views'
	, SUM(t.[Count])            AS 'Tag_Sum'
	, ISNULL(SUM(c.[Score]), 0) AS 'Comment_Sum'
	, ISNULL(COUNT(se.[Id]), 0) AS 'Suggested_Edits_Count'
FROM
    [Posts] p
    INNER JOIN [Posts] p1
        ON p.[ParentId] = p1.[Id]
    INNER JOIN [Users] u
        ON p.[OwnerUserId] = u.[Id]
	LEFT JOIN [Comments] c
        ON p.[Id] = c.[PostId]
	LEFT JOIN [SuggestedEdits] se
		ON p.[Id] = se.[PostId]
    INNER JOIN [PostTags] pt
        ON pt.[PostId] = p1.[Id]
	INNER JOIN [Tags] t
        ON t.[Id] = pt.[TagId]
WHERE
             p.[PostTypeId]     =    2 -- Answer.
    AND YEAR(p.[CreationDate])  = 2018
    AND YEAR(p1.[CreationDate]) = 2018
	AND     (p.[Score]          >    5
    OR       p.[Score]          <    2)
GROUP BY
	  p.[Id]
    , p1.[Score]
    , p1.[CommentCount]
    , p1.[ViewCount]
    , p1.[Tags]
    , p1.[AcceptedAnswerId]
    , p.[Score]
    , p.[Body]
    , p.[CommentCount]
    , u.[Reputation]
    , u.[Views]
HAVING
	    COUNT(se.[Id]) > 0
    AND SUM(c.[Score]) > 0
ORDER BY
    p.[Score] DESC;

Enter Parameters

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