0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @start date = dateadd(week, -1, getdate())

select
 concat('http://stackoverflow.com/questions/', p.Id)
,p.Title
,p.Score
,sum(iif(q.AcceptedAnswerId is null, 0.0, 1.0)) as UserAcceptedAnswers
,format(sum(iif(q.AcceptedAnswerId is null, 0.0, 1.0)) / nullif(count(q.Id),0), 'P2') as UserAcceptRate
from Posts as p
  join Users as u
    left join Posts as q
    on q.OwnerUserId = u.Id
  on u.Id = p.OwnerUserId
where p.PostTypeId = 1 -- question
and p.CreationDate >= @start
and p.Score > 0
and p.AcceptedAnswerId is null
and (
    p.Tags like '%<c#>%'
   or p.Tags like '%<javascript>%'
   or p.Tags like '%<jquery>%'
   or p.Tags like '%<css>%'
   or p.Tags like '%<html5>%'
   or p.Tags like '%<sql>%'
)
and p.Tags not like '%<php>%'
and p.Tags not like '%<python>%'
and p.Tags not like '%<ruby>%'
and p.Tags not like '%<perl>%'
group by
 p.Id
,p.Score
,p.Title
,p.Tags
,u.DisplayName
having sum(iif(q.AcceptedAnswerId is null, 0.0, 1.0)) > 2
and sum(iif(q.AcceptedAnswerId is null, 0.0, 1.0)) / nullif(count(q.Id),0) > .5
order by
 UserAcceptedAnswers desc
,UserAcceptRate desc
,p.Score desc

view current version

edited oct 13 14

created oct 13 14 anonymous