Unsung Hero Hunter

1

Please login or register to vote for this query.

(click on this box to dismiss)

Find unanswered questions for tags you're interested in. Show users who CAN'T upvote your question, but who can only mark as accepted. This query uses the top 25 tags that you're active in, by your User ID. NumAnswersThreshold is the number of answers that the question currently has. RecentlySeenInLastDaysThreshold = the number of days previous that S.O. has seen this user. This helps filter out people who don't visit the site regularly. Remember that these are as-of the last StackOverflow data dump!

Stack Overflow

Q&A for professional and enthusiast programmers

-- Unsung Hero Hunter
-- Find unanswered questions for tags you're interested in. Show users who CAN'T upvote your question, but who can only mark as accepted.
-- This query uses the top 25 tags that you're active in, by your User ID.
-- NumAnswersThreshold is the number of answers that the question currently has.
-- RecentlySeenInLastDaysThreshold = the number of days previous that S.O. has seen this user. This helps filter out people who don't visit the site regularly.
-- Remember that these are as-of the last StackOverflow data dump!

DECLARE @UserID int = ##UserID##
         ,@AnswerThreshold int = ##NumAnswersThreshold##
        ,@SeenInLastNumDays int = ##RecentlySeenInLastDaysThreshold##

SELECT * FROM (
SELECT TOP 500  p.id AS  [Post Link], 
                p.CreationDate,
                (SELECT COUNT(*) FROM Posts WHERE ParentID = P.ID) AS NumAnswers,
                            
                u.DisplayName AS UserAsking, 
                u.Reputation AS UserReputation, 
                u.LastAccessDate
from posts as p 
inner join users as u on p.owneruserid = u.id
inner join posttags as pt on pt.postid = p.id
inner join tags as t on t.id = pt.tagid
 
where u.reputation BETWEEN 2 AND 15 --user has SOME rep.
and p.acceptedanswerid is null
and P.PostTypeID = 1 --is a question.
AND U.LastAccessDate >= DATEADD(dd,-1* @SeenInLastNumDays, getdate())
AND  T.TagName in (
                     select TOP 25 tagname 
                     from posts as pp 
                     inner join posttags as pt on pt.postid = pp.id
                     inner join tags as t on t.id = pt.tagid
                     WHERE pp.ID IN ( 
                          --you answered
                          SELECT ParentID 
                          FROM  Posts 
                          WHERE  posttypeid = 2 
                          AND  owneruserid = @userID  

                          UNION 

                          SELECT ID -- you asked
                          FROM Posts WHERE owneruserid = @userID 
                          AND PostTypeID = 1
                          )

                     GROUP BY TagName 
                     ORDER BY count(*)  DESC
 
                     )
GROUP BY  p.id, p.CreationDate,
         
                u.DisplayName, 
                u.Reputation, 
                u.LastAccessDate
) AS A
WHERE NumAnswers <= @AnswerThreshold 
order by CreationDate DESC

Enter Parameters

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