1

Please login or register to vote for this query.

(click on this box to dismiss)

Finds questions that have a specified tag and do not have answers with greater than the one provided. This is a fork of https://data.stackexchange.com/stackoverflow/query/4038/find-interesting-unanswered-questions but the weight takes into account answerer's reputation, post score and views count.

Q&A for community managers, administrators, and moderators

DECLARE @UserId int = ##UserId##
DECLARE @TagQuery nvarchar(35) = '##Tag##'
DECLARE @AnswerScore int = ##AnswerScore?-1##

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

insert #tags 
SELECT 
    Id,
    TagName
FROM Tags
WHERE 
    TagName = @TagQuery

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 > @AnswerScore) = 0
  and CommunityOwnedDate is null 
  and ClosedDate is null 
  and q.ParentId is null 
  and AcceptedAnswerId is null

select top 2000 u.Id as [Post Link], 
(us.Reputation / 200.0 + p.Score * 100 + p.ViewCount * 10) 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  
group by u.Id, us.Reputation, p.Score, p.ViewCount 
order by Weight desc

fork query

edited feb 28 19

created feb 28 19 pckill