Finding Unknown Heroes in Stack Overflow using Data Explorer

Question

Finding Unknown Heroes in Stack Overflow using Data Explorer

Asked 10 years, 3 months ago

Viewed 266 times

7

I know there’s no Unknown Hero here at SOPT, but I know there are several at SOEN. My intention is to sort the percentage of responses accepted without votes of the people who hold this medal. And so I tried to create an SQL for the Data Explorer. But I don’t know SQL and I just tried to put together several examples and try to see if I could.

The query I tried (I think it’s the closest I got):

SELECT TOP 100
    Id as [User Link],
    count(a.Id) as [Respostas Aceitas],
select
    sum(CASE WHEN a.Score = 0 then 1 else 0 end)*1000 / count(a.Id) / 10.0 as [Porcentagem]
from
    Users 
    inner join
    Posts q
    inner join
    Posts a
    on a.Id = q.AcceptedAnswerId and q.AcceptedAnswerId = Users.UserID
where
      a.CommunityOwnedDate is null
      and a.postTypeId = 2
order by Porcentagem

Error message:

Incorrect syntax near the keyword 'select'. Incorrect syntax near the keyword 'Where'.

What is the result of the query? You can put a link to your test in the DE?

– brasofilo

2014/08/18 at 21:57
@Brasofilo, I have no way to put the link, because I could not log there with facebook and also think that it does not save a query mistakenly.

– DeMarco

2014/08/18 at 22:13
I’ve adjusted the syntax of your query, but there’s a Join I don’t understand. p.: (q.AcceptedAnswerId = Users.UserID). Here is the link: http://data.stackexchange.com/stackoverflow/query/edit/218881. Some things are still missing.

– Wakim

2014/08/18 at 22:57
1

Do you simply want who has the medal? If it is, it has a table Badges, just look at her.

– bfavaretto

2014/08/19 at 02:14
Friend, which database?

– SneepS NinjA

2014/08/19 at 20:22
@bfavaretto explained better in that comment

– DeMarco

2014/08/19 at 22:52
2

Okay, I edited the question by adding that information. Always prefer to edit the question to add information in comments, because nothing guarantees that an obsessive-compulsive moderator arrives and clears all comments of the question (don’t look at me...) :)

– bfavaretto

2014/08/19 at 22:55

Show 2 more comments

1 answer

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.

by rla4 • **358** points · Answer 1 · 2014-08-19T20:25:21+00:00

As @bfavaretto suggested, if your intention was just to return users who have this medal, just look directly at the Badges table:

select u.DisplayName from Users u
    inner join Badges b on u.Id = b.UserId
    where b.Name like 'Unsung Hero'

The names of the medals are not translated into the bank - hence the use of the original name ("Unsung Hero"). To see which would be the possible candidates for this medal (that is, with at least 10 answers accepted), ordered by the percentage, the query below should work:

select  TOP 100 
        a.OwnerUserId as UserId,
        sum(case when a.Score = 0 then 0 else 1 end) as [Respostas Com Votos],  
        sum(case when a.Score = 0 then 1 else 0 end) as [Respostas Sem Votos],
        sum(CASE WHEN a.Score = 0 then 1 else 0 end)*1000 / count(a.Id) / 10.0 as [Percentual de Sem Votos]
    from
        Posts q
      inner join
        Posts a
      on a.Id = q.AcceptedAnswerId
    where
          a.CommunityOwnedDate is null
      and a.OwnerUserId != q.OwnerUserId
      and a.postTypeId = 2      
    group by a.OwnerUserId
    having count(a.Id) >= 10
    order by 4 desc