Other users who frequent the SAME TAGS that you do

2

Please login or register to vote for this query.

(click on this box to dismiss)

See TAB#2 for your "top tags" as used for this query. ("Actions" = posts or comments on questions or answers related to the tag)

Emacs

Q&A for those using, extending or developing Emacs

declare @startDate datetime = dateadd(d,0-##daysBack:int?30##,( select min(create_date) from sys.tables ))
create table #myTags (userid int, tagid int)
create table #yourTags (userid int, tagid int)

insert into #myTags (userid, tagid) select userid, tagid   -- my comments
from posts p 
  left join comments c on c.postid=p.id 
  left join posttags pt on isnull(p.parentid,p.id)=pt.postid
where c.creationdate>=@startDate and c.userid=##UserID## 

insert into #myTags (userid, tagid) select    --my posts
  p.owneruserid as userid, tagid
from posts p left join posttags pt on isnull(parentid,id)=pt.postid
where p.creationdate>=@startDate and p.owneruserid=##UserID##
 
select top 10 -- MAX 10 TAGS
  userid, tagid, count(*) as actions
into #myTopTags from #myTags
group by userid, tagid
having count(*)>=10 --min. 10+ interactions with the tag
order by count(*) desc

---------------------------------------------------------

insert into #yourTags (userid, tagid) select userid, tagid  --their comments
from posts p 
  left join comments c on c.postid=p.id 
  left join posttags pt on isnull(p.parentid,p.id)=pt.postid
where c.creationdate>=@startDate and tagid in ( select tagid from #myTopTags )

insert into #yourTags (userid, tagid) select    --their posts
  p.owneruserid as userid, tagid
from posts p left join posttags pt on isnull(parentid,id)=pt.postid
where p.creationdate>=@startDate and tagid in ( select tagid from #myTopTags )

select userid, tagid, count(*) as actions
into #yourTopTags from #yourTags
group by userid, tagid
having count(*)>=10 --min. 10+ interactions with the tag
order by count(*) desc 

select 
  y.userid as [User Link],    --TAB #1 : Other users of your tags
  string_agg(t.tagname,' ') as Tags,
  sum(y.actions) as actions,
  case when userid=##UserID## then N'👈 👈 👈  ' else N'        ' end as ". . . . . ."
from #yourTopTags y join tags t on y.tagid=t.id
group by y.userid 
order by sum(y.actions) desc

select userid as [User Link], actions, tagname --TAB #2 : Your Top Tags 
from #myTopTags m join tags t on m.tagid=t.id
order by actions desc



--daysBack:How many days back to look for activity?"How recent should included tag activity be?"
--UserID:Enter your numeric User ID for this site:"Your user ID is the number within the URL for your user profile."

Enter Parameters

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