2

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.

|improve this question
  • Something like this? data.stackexchange.com/stackoverflow/query/143311/… – Qantas 94 Heavy Oct 27 '13 at 13:38
  • Qantas looking for equivalent query for this # hasaccepted:0 user:139150 answers:1 – shantanuo Oct 27 '13 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 – Bill the Lizard Oct 27 '13 at 13:41
  • 2
    Wow. Someone who's actively looking for questions they can accept and they get downvoted. – Chris Gerken Oct 27 '13 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 – Jeroen Vannevel Oct 27 '13 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♦ Oct 27 '13 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... – Lorem Ipsum Oct 27 '13 at 17:00
  • 1
    data.stackexchange.com/stackoverflow/query/144706 – Some Helpful Commenter Oct 27 '13 at 17:29
  • 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♦ Oct 27 '13 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. – Lorem Ipsum Oct 27 '13 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♦ Oct 27 '13 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. – Dukeling Oct 28 '13 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 Oct 30 '13 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. – Dukeling Oct 30 '13 at 8:15
  • totally agreed @duke! Just didn't want potential fiddlers to be discouraged. – jmac Oct 30 '13 at 9:23
2

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 (

Select 
      a.id ,
      a.Score,
      RANK() OVER(PARTITION BY q.id ORDER BY a.score desc) rk
From 
       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##
  )
Select 
  id [Post Link],
  Score
From Questions
Where
   rk = 1

Notes

  • 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
|improve this answer

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