0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

with users as (
      -- users who asked a question with the given tag
      select distinct OwnerUserId user_id
      from posts
      where creationdate >= '2010-01-01'
        and tags like '%<perl%'
     union all
      -- users who answered to the questions with the given tag
      select distinct p2.OwnerUserId user_id
      from posts p1 left join posts p2 on p1.id = p2.parentid
      where p2.creationdate >= '2010-01-01'
        and p1.tags like '%<perl%'
     union all
      -- users who made an edit to the questions with the given tag
      select distinct LastEditorUserId user_id
      from posts
      where LastEditDate >= '2010-01-01'
        and tags like '%<perl%'
     union all
      -- users who made an edit to the answers of questions with the given tag
      select distinct p2.LastEditorUserId user_id
      from posts p1 left join posts p2 on p1.id = p2.parentid
      where p2.LastEditDate >= '2010-01-01'
        and p1.tags like '%<perl%'
     union all
      -- users who commented under questions with the given tag
      select distinct c.userid user_id
      from posts p join comments c on p.id = c.postid 
      where c.creationdate >= '2010-01-01'
        and p.tags like '%<perl%'
     union all
      -- users who commented under answers to questions with the given tag
      select distinct c.userid user_id
      from posts p2 right join posts p1 on p1.id = p2.parentid
                          join comments c on p2.id = c.postid 
      where c.creationdate >= '2010-01-01'
        and p1.tags like '%<perl%'
      )
    select 'Perl' programming_language, count(distinct user_id) distinct_users_count
    from users

fork query

created jul 12 20 jon.doe33167