Feedback on a user's posts

1

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 feedbackByPost AS (
    SELECT      f.PostId,
                Sum (CASE  WHEN f.VoteTypeId = 2 THEN 1 ELSE 0 END) AS Helpful,
                Sum (CASE  WHEN f.VoteTypeId = 3 THEN 1 ELSE 0 END) AS Unhelpful
    FROM        PostFeedBack f
    JOIN        Posts       p
    ON          p.id        = f.PostId
    WHERE       OwnerUserId = ##userid##
    GROUP BY    f.PostId
)
SELECT      p.Id AS [Post Link],
            CASE    WHEN p.PostTypeId = 1 THEN 'Q'
                    WHEN p.PostTypeId = 2 THEN 'A'
                    ELSE 'other'
            END
            AS [Type],
            f.Helpful - f.Unhelpful AS [FB score],
            p.Score,
            CASE    WHEN p.PostTypeId = 2 THEN
                        CASE    WHEN p.Id = q.AcceptedAnswerId THEN 'yes'
                                ELSE 'no'
                        END
                    ELSE 'NA'
            END
            AS [Accepted?],
            COALESCE (p.ViewCount, q.ViewCount) AS [Views],
            f.Helpful,
            f.Unhelpful
FROM        Posts           p
JOIN        feedbackByPost  f
ON          p.Id            = f.PostId
LEFT JOIN   Posts           q
ON          p.ParentId      = q.Id
ORDER BY    f.Helpful - f.Unhelpful,
            p.Score,
            p.ViewCount,
            p.Id

Enter Parameters

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