I want to study how people behave where they are near to get a Tag Badge, so I need to:
- Extract all the ID of users who have won a Tag Badge in a certain period of time.
- See what Tag Badge they have won
- 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.