my tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

-- Find interesting unanswered questions
-- Looks at unanswered questions in your top 20 tags and sorts them by
-- a combined weight which takes into account: score, askers reputation and how
-- well you do on that particular tag

DECLARE @UserId int = 343761 -- ##UserId##

create table #tagnames (TagName nvarchar(35), Weight int)
insert into #tagnames VALUES 
  ('javascript', 2),
  ('haskell', 6),
  ('lodash', 7), ('underscore.js', 7),
  ('ramda.js', 10),
  ('jquery', 0)
  

create table #tags (TagId int, TagName nvarchar (35), Weight int)

insert #tags 
SELECT TOP 20 
    Id as TagId,
    G.TagName,
    t.Weight
FROM Tags G
INNER JOIN #tagnames t on t.TagName COLLATE DATABASE_DEFAULT  = G.TagName COLLATE DATABASE_DEFAULT  

create table #unanswered (Id int primary key)


insert #unanswered 
select q.Id  from Posts q
where (select count(*) from Posts a where a.ParentId = q.Id and a.Score > 0) = 0
and CommunityOwnedDate is null and ClosedDate is null and q.ParentId is null 
and AcceptedAnswerId is null

create table #ps (Id int, Weight float)

insert #ps
select top 2000 u.Id, 
(sum(t.[Weight]) / datediff(hh,p.CreationDate,getdate())  ) as Weight 
from #unanswered u
join Posts p on u.Id = p.Id
join PostTags pt on pt.PostId = u.Id
join #tags t on t.TagId = pt.TagId  
join Users us on us.Id = p.OwnerUserId  
where  us.Reputation > 30 and t.Weight > 0
group by u.Id, p.CreationDate
order by Weight desc 


select ps.Id as [Post Link], p.Score, p.ViewCount, p.Tags, p.Body 
from #ps ps inner join Posts p on p.Id = ps.Id

Enter Parameters

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