Problems filtering rows with Stack Exchange Data Explorer (SEDE)

Question

I want to study how people behave where they are near to get a Tag Badge, so I need to:

1. Extract all the ID of users who have won a Tag Badge in a certain period of time.
2. See what Tag Badge they have won
3. Analyze for each user, and corresponding tag, the historic series of answers.

I have no idea about how to filter rows in order to satisfy point 3. This is what I wrote:

select posts.owneruserid as 'User',
       posts.creationdate as 'Answer Date', 
       tags.tagname as 'Tag Name'
from posts
join posttags on posts.parentid=posttags.postid
join tags on posttags.tagid=tags.id
where posts.posttypeid=2 
      and 
      posts.owneruserid in ( select top 100 badges.userid
                             from badges
                             where badges.tagbased=1
                             and
                             badges.date > '2016-01-01 00:00:00'
                           )
 order by posts.owneruserid desc

With this, I query all the answers of (top 100) users, not just ones corresponding to the tag they won.

Answer 1

Something like this would work, using a temporary table:

create table #badges (userid int, name nvarchar(50) collate SQL_Latin1_General_CP1_CS_AS);
insert into #badges
select top 100 userid, name
  from badges
  where badges.tagbased = 1
    and badges.date > '2016-01-01 00:00:00';

select posts.owneruserid as 'User',
       posts.creationdate as 'Answer Date', 
       tags.tagname as 'Tag Name'
from posts
join posttags on posts.parentid = posttags.postid
join tags on posttags.tagid = tags.id
join #badges on #badges.userid = posts.owneruserid
 and #badges.name = tags.tagname
where posts.posttypeid = 2
 order by posts.owneruserid desc

Note the specified collation to avoid the problem mentioned here.

I was afraid that not limiting the badges would cause the query to timeout, but apparently it doesn't, so this is probably what you want in the end.

select posts.owneruserid as 'User',
       posts.creationdate as 'Answer Date', 
       tags.tagname as 'Tag Name'
from posts
join posttags on posts.parentid = posttags.postid
join tags on posttags.tagid = tags.id
join badges on badges.userid = posts.owneruserid
 and badges.name = tags.tagname
where posts.posttypeid = 2
  and badges.tagbased = 1
  and badges.date > '2016-01-01 00:00:00'
 order by posts.owneruserid desc

Happy querying!