Finding Unknown Heroes in Stack Overflow using Data Explorer

Question

Finding Unknown Heroes in Stack Overflow using Data Explorer

Navigation

#1 by (9 votes)

7

I know there are no Unknown Hero here in SOPT, but I know there are several not SOEN. My intention is to rank the percentage of votes accepted without the votes of the people holding this medal. And so I tried to create a SQL for Data Explorer . But I do not know SQL and I just tried to gather several examples and try to verify if I could.

The query I tried (I think it's the closest I've come):

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'.

sql

asked by anonymous 18.08.2014 / 23:12

1 answer

Join two tables with PHP Theoretical doubt - Interface, single responsibility

score 9 · Accepted Answer

As suggested by @bfavaretto, if your intention was just to return users who have this medal, it would be enough to 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 what would be the possible candidates for this medal (that is, with at least 10 accepted answers), sorted 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