0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for artists and crafters

with candidate_posts as (
    select distinct
        a.owneruserid as userid,
        a.id,
        case 
          when a.body like '<p>Actually, %' 
            then 1.0
            else 0.0
        end as match
    from posts as a
        inner join posts as q
        on a.parentid = q.id and q.posttypeid = 1 and a.posttypeid = 2
)
select
    userid as [User Link],
    format(sum(match) / count(id), 'P') as perc,
    round(sum(match) / count(id) * sum(match), 2) as ratio,
    count(id) as total,
    cast(sum(match) as int) as matches
from candidate_posts
group by userid
having count(id) >= 5 and sum(match) > 2
order by round(sum(match) / count(id) * sum(match), 2) desc

fork query

edited nov 28 anonymous

created nov 27 Martijn Pieters