1

I am looking into constructing a T-SQL query in Data Explorer for Stack Exchange that would allow me to find out a specific Stack Overflow user's tag that he/she has interacted with the most. I have tried this, but it returns empty rows, garbled up information that I don't want. I want to modify my query in such a way that I get the number (int) of the tag Jon Skeet is most associated with and obviously the tag name.

select users.id, posts.tags 
from users
inner join posts
on users.id = posts.owneruserid
where users.id = 22656

Here is what this query returns. Please check the image below.

Enter image description here

  • 1
    Or just look directly at his profile to find out - stackoverflow.com/users/22656/jon-skeet?tab=tags – JonW Dec 21 '13 at 12:50
  • Check my edit please. – Ali Gajani Dec 21 '13 at 12:54
  • I specifically want to do this with data explorer. It's for an more superior goal than this (hint: data science fun). – Ali Gajani Dec 21 '13 at 12:55
  • @AliGajani I've added mention of that to your question. - Or... not, you just overwrote that edit. Mind mentioning it if you're still editing? Otherwise you'll have more answers like mine from people saying "just visit their profile". – doppelgreener Dec 21 '13 at 12:56
  • What does that do? – Ali Gajani Dec 21 '13 at 12:57
  • 1
    You'll need to use the PostTags table. The tags is just a denormalized column that can't be efficiently used. – Martin Smith Dec 21 '13 at 13:01
  • Cool, I'll look into that. I would love if you could elaborate on this with a bit more SQL, but thanks. – Ali Gajani Dec 21 '13 at 13:05
9

Your problem is almost certainly that most of Jon Skeet's posts are answers, and answers don't have tags; questions do. You'll need to look up the question for any answer posts, and use the tags there to get the data you want.

| improve this answer | |
  • How would that be, could you elaborate in SQL? – Ali Gajani Dec 21 '13 at 13:01
  • Unfortunately, I'm on my phone at the moment, so the actual solution is a bit beyond what I can manage right now. From this comment it looks like there's a table with the info already in there. – Billy Mailman Dec 21 '13 at 13:02
  • Thank you so much for attempting to answer though. Appreciated :) – Ali Gajani Dec 21 '13 at 13:04
8

Something along the following lines should do it.

WITH UserPosts
     AS (SELECT *
         FROM   posts
         WHERE  owneruserid = ##UserId##),
     QuestionIds
     AS (SELECT Id
         FROM   UserPosts
         WHERE  PostTypeId = 1 /*Use Id for questions*/
         UNION ALL
         SELECT ParentId
         FROM   UserPosts
         WHERE  PostTypeId = 2 /*Use ParentId for answers*/),
     TagCount
     AS (SELECT COUNT(*) AS Cnt,
                TagId
         FROM   PostTags
         WHERE  PostId IN (SELECT Id
                           FROM   QuestionIds)
         GROUP  BY TagId)
SELECT TagName,
       Cnt
FROM   TagCount TC
       JOIN Tags T
         ON TC.TagId = T.Id
ORDER  BY Cnt DESC 

This counts each question they have participated in as being an "interaction".

If you want to count self answered questions as being two interactions (or multiple answers to the same question separately) you would need INNER JOIN instead.

| improve this answer | |
  • That's what I was looking for. Thanks Martin. – Ali Gajani Dec 21 '13 at 13:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .