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 '%swift%'
                           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 '%swift%'
                                                    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 '%swift%'
                                                                             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 '%swift%'
                                                                                                      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 '%swift%'
                                                                                                                               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 '%swift%'
                                                                                                                                                                                 )
                                                                                                                                                                                   select 'Swift' programming_language, count(distinct user_id) distinct_users_count
                                                                                                                                                                                     from users

view current version

edited jul 11 20

created jul 11 20 jon.doe33167