CODEWITHSUNDEEP
sqlsql-serverdata.stackexchange.com
styts
styts

Reputation: 1023

Generalize SQL query to include data for all Users

This popular data.stackexchange.com query allows me to query all tags of a user combined with their upvotes.

E.g. for user 22656 the follwing is outputed:

TagName, Upvotes
c#, 147346
java, 84373
.net, 49952

I would like to extract this information for all (or a large subset of) users. Can somebody help me generalize this query?

Example output could be:

user1, {"tagA": 6, "tagB": 4, ...}
user2, {"tagX": 7, "tagY": 3, ...}
...
userN, {"tagB": 3, "tagA": 1, ...}

Upvotes: 1

Views: 80

Answers (1)

juergen d
juergen d

Reputation: 204766

SELECT Posts.OwnerUserId,
       TagName,
       COUNT(*) AS UpVotes 
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2
GROUP BY Posts.OwnerUserId, TagName 
ORDER BY UpVotes DESC

Upvotes: 1

Related Questions