1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

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
    inner join posttags as pt on pt.postid = q.id
    inner join tags as t on t.id = pt.tagid
    where
      t.tagname in (
        'javascript',
        'react-native',
        'reactjs',
        'node.js',
        'xcode',
        'android'
      )
)
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 27

created nov 27 Martijn Pieters