3

I am looking to try and reduce the number of unanswered questions on the UXSE site by identifying high quality questions that hasn't been addressed probably because it is either difficult to answer or has just been lost in amongst a lot of the other more popular questions.

Has anyone written a search query that will list the following search results?

  • count of unanswered questions by users (sorted from highest to lowest or top 50 for example)
  • list of unanswered questions ordered by longest period without answers (or comments)
  • list of questions with most amount of bounties placed on the question (without an accepted answer)

And if you can think of other ways to identify good questions that should be answered feel free to add it to the list.

  • 1
    You could look for the owners of Curious, Inquisitive, and Socratic badges (depending on the size of the site: small sites may have fewer Socratic badges). The great-question and famous-question badges (and related bronze and silver badges) are not good indicators because it's possible that the user has only one well-received question while the others are all negatively received (this is frequent for people who write poorly-researched questions and chance upon an HNQ). Socratic/Inquisitive badges tend to indicate consistently good questions. – Chair Oct 6 '18 at 12:08
  • "I am looking to try and reduce the number of unanswered questions on the UXSE site by identifying high quality questions that hasn't been addressed" FYI, SE already has "Unanswered" questions list – Bookends Oct 6 '18 at 15:42
  • @Somewhat the unaswered questions list isn't always filled with questions that should be answered though... many are just not structured well enough to be answered (and probably needs to be cleaned up) while others probably don't belong in UXSE. That's not to say I don't look there every so often. – Michael Lai Oct 7 '18 at 10:27
1

These can be answered with some Stack Exchange Data Explorer magic.

count of unanswered questions by users (sorted from highest to lowest or top 50 for example)

That can be retrieved with this query

select q.owneruserid as [User Link]
     , count(*) [#unanswered questions]
from posts q
where q.posttypeid = 1 -- Q
and answercount = 0 -- no answers
and q.owneruserid is not null
and q.score > 0 -- "High" quality
and q.closeddate is null -- not closed
group by q.owneruserid
order by count(*) desc

list of unanswered questions ordered by longest period without answers (or comments)

That can be retrieved with this query

select q.id as [Post Link]
     , creationdate
     , datediff(d,getdate(), q.creationdate) [# days unanswered]
from posts q
where q.posttypeid = 1 -- Q
and q.answercount = 0 -- no answers
and q.commentcount = 0 -- no comments
and q.closeddate is null -- not closed
-- not merged
and not exists (
  select *
  from posthistory ph
  where ph.postid = q.id
  and ph.posthistorytypeid = 37 -- Post Merge Source
  )
-- sorting
order by datediff(d,getdate(), q.creationdate)

list of questions with most amount of bounties placed on the question (without an accepted answer)

That can be retrieved with this query

select q.id as [Post Link]
     , count(*) [# bounties]
     , sum(v.bountyamount) [Total Bounty amount]
from posts q
inner join votes v on v.postid = q.id
where q.posttypeid = 1 -- Q
and q.closeddate is null -- not closed
and acceptedanswerid is null
and v.votetypeid = 8 -- Bounty Start
group by q.id
-- sorting
order by count(*) desc

Keep in mind that SEDE is only updated once a week, on Sunday morning. Learn how to create your own queries by following the awesome tutorial. For a chat about SEDE, visit our chatroom and say "Hi!".

  • +1 I don't think you could have answered my question any better! – Michael Lai Oct 7 '18 at 13:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .