In the Data Explorer, how do I find the questions I have asked, which are answered but not accepted by me?

In search, I could do something like:

hasaccepted:0 user:id answers:1

I am looking for a similar Data Explorer query.

  • Something like this? data.stackexchange.com/stackoverflow/query/143311/… Commented Oct 27, 2013 at 13:38
  • Qantas looking for equivalent query for this # hasaccepted:0 user:139150 answers:1
    – shantanuo
    Commented Oct 27, 2013 at 13:39
  • 3
    There's an API route specifically for this, if that's any help to you. api.stackexchange.com/docs/unaccepted-questions-on-users Commented Oct 27, 2013 at 13:41
  • 2
    Wow. Someone who's actively looking for questions they can accept and they get downvoted. Commented Oct 27, 2013 at 15:08
  • 1
    Just in case you don't know: the questions that have an answer have a green background, a number > 0 and this number is slightly colored. Questions that don't have an answer have a green background, a number > 0 and a white number. You can look trough our own history if you wish to do the manual route: stackoverflow.com/users/139150/shantanuo?tab=questions Commented Oct 27, 2013 at 15:54
  • @Chris I think others disagree with that. He already knows how to find his unaccepted questions which have answers, and for some reason wants a Data Explorer query that will also do that (and even give him slightly less accurate information since it's only updated weekly). I'd imagine users don't see the point in the question.
    – animuson StaffMod
    Commented Oct 27, 2013 at 16:24
  • @animuson The question certainly has merit, so downvoting it seems rather silly. You can construct more complex queries using the SEDE than you can with search. For instance, what if he wanted to add a condition that the answer provided must have at least X votes? You can't do that with search... Commented Oct 27, 2013 at 17:00
  • 1
  • 2
    @yoda Then he should specify that criteria, or indicate that he's planning to further modify the query. He hasn't indicated any problem with using Data Explorer other than wanting the query. At face value, it's more of a "how do I translate this from lamguage x to language y" question that would be closed on Stack Overflow. If he has a specific problem with Data Explorer, he really should explain it in more detail.
    – animuson StaffMod
    Commented Oct 27, 2013 at 17:40
  • @animuson Why? Perhaps he knows how to extend it and only wants a basic example... We always want users to whittle down their problem to the bare basics and not overload with details. The extension is one such detail that is immaterial to the answer to this specific question. This is a site for questions on Stack Exchange's services and is appropriately tagged as support. If there's a duplicate then link to that, but it's not fair to close a support question just because the answer is boring. Commented Oct 27, 2013 at 18:55
  • @yoda We're not ralking about closing it, we're talking about reasons why someone might downvote it. It's a perfectly valid question, just not one that really warrants an answer. If anything, the answer is "you should use the search because it returns more up-to-date results and can handle the exact criteria you've mentioned."
    – animuson StaffMod
    Commented Oct 27, 2013 at 19:04
  • 3
    @ChrisGerken One reason for downvoting - this is a question asking for someone to write a basic SQL query for them. And, in my opinion, if you don't know basic SQL, Data Explorer isn't for you. Commented Oct 28, 2013 at 1:05
  • @Duke, I actually think the data explorer is a good way to learn SQL since you have so many examples and can kind of stumble through (I certainly didn't know any SQL when I started, but can now write basic queries thanks to fumbling around with it!)
    – jmac
    Commented Oct 30, 2013 at 5:12
  • 1
    @jmac I don't disagree with your reasoning, but I think you should look at examples, try to write your own query and, if you have a problem with it, probably ask on Stack Overflow (following the normal question-asking guidelines) (currently this question is not all that different from write-my-code-for-me questions on Stack Overflow). And OP does seem to know SQL. Commented Oct 30, 2013 at 8:15
  • totally agreed @duke! Just didn't want potential fiddlers to be discouraged.
    – jmac
    Commented Oct 30, 2013 at 9:23

1 Answer 1


As mentioned in the comments it's a pretty straight forward query. That said there are some things we should know that can make this eaiser

  • Posts.OwnerUserID Will tell you which posts are yours
  • Posts.ParentId Only Answers have non-null values. We'll take advantage of this to avoid PostTypeID filters
  • Posts.AcceptedAnswerID Is Null when there are no accepted answers.
  • [Post Link] is a magic column that will create links to content
  • ##UserId## If you use this name Data Explorer will look up your email and if it matches your email on the SE accounts will populate it with the correct user id
  • ##MinAnswerScore:Int?0## this will create a integer parameter with the default value of zero

Using the above I constructed the following query

With Questions as (

      a.id ,
      RANK() OVER(PARTITION BY q.id ORDER BY a.score desc) rk
       posts q
       INNER JOIN posts a
       ON q.id = a.ParentId

Where q.OwnerUserID = ##UserId##
      And q.acceptedanswerid is null
      And a.Score >= ##MinAnswerScore:Int?0##
  id [Post Link],
From Questions
   rk = 1


  • The from clause FROM posts q INNER JOIN posts a ON q.id = a.ParentId ensures that all posts q are's questions that have at least one answer
  • RANK() OVER(PARTITION BY q.id ORDER BY a.score desc) rk combined with where rk = 1 returns only the highest scored answers with the query. In case of ties for highest more than answer will be returned. (If you want only one change rank() to row_number())

When run for you with a minimum of 5 here are the results

Post Link                       Score 
------------------------------- ----- 
Converting variable to integer  5     
replace space in URL JavaScript 6     
escape single quotes            19    

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