SELECT Posts.Id as [Post ID], ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

SELECT 
    Posts.Id as [Post ID],                              -- Unique Post ID
    Tags.TagName as [Searched Tag],                     -- Searched Tag
    COUNT(DISTINCT Answers.Id) as [Answer Count],       -- Count unique answers
    SUM(Answers.Score) as [Total Answer Score],         -- Sum of answer scores
    CAST(AVG(CAST(Answers.Score AS float)) as numeric(6,1)) as [Average Answer Score],
    Posts.Tags as [Tagged With],
    Posts.Title as [Title],
    Posts.Body as [Body],
    Posts.CreationDate as [Creation Date],
    Users.DisplayName as [User Name],
    Users.Location as [User Location],
    Users.Views as [User Views],
    Users.Reputation as [User Reputation],
    Posts.ViewCount as [Post View Count],
    Posts.Score as [Post Score],
    Posts.AnswerCount as [Total Answer Count],
    Posts.AcceptedAnswerID as [Accepted Answer ID]
FROM Posts
-- Joining tables to get necessary details
JOIN PostTags ON PostTags.PostId = Posts.Id
JOIN Tags ON Tags.Id = PostTags.TagId
JOIN Users ON Users.Id = Posts.OwnerUserId
LEFT JOIN Posts AS Answers ON Answers.ParentId = Posts.Id   -- Getting related answers
WHERE Tags.TagName LIKE '%talkback%'                       -- Filter posts with 'usability' tags
GROUP BY 
    Posts.Id, Tags.TagName, Posts.Tags, Posts.Title, 
    Posts.Body, Posts.CreationDate, Users.DisplayName, 
    Users.Location, Users.Views, Users.Reputation, 
    Posts.ViewCount, Posts.Score, Posts.AnswerCount, 
    Posts.AcceptedAnswerID
ORDER BY 
    SUM(Answers.Score) DESC;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results