.gitignore

*swp

active editors from accounts created on mobile.sql

/* users who signed up in month X and reached 5 edits within 30 days */
 select DATE_FORMAT(CONCAT(month, '01'), '%Y-%m-%d') as month,
        count(*) as editors_with_five_edits_within_30_days
   from (select ssac.event_userId as user_id,
                EXTRACT(YEAR_MONTH from ssac.timestamp) as month
           from log.ServerSideAccountCreation_5487345 ssac
                    inner join
                enwiki.revision rev     on ssac.event_userId = rev.rev_user
                                        and rev.rev_timestamp > ssac.timestamp
                                        and rev.rev_timestamp <= DATE_FORMAT(DATE_ADD(ssac.timestamp, INTERVAL 30 DAY), '%Y%m%d000000')
                    inner join
                enwiki.page page        on page.page_id = rev.rev_page
          where ssac.wiki = 'enwiki'
            and ssac.event_displayMobile = 1
            and ssac.timestamp >= DATE_SUB(now(), INTERVAL 10 MINUTE)
            and page.page_namespace = 0
          group by ssac.event_userId
         having count(*) >= 5
        ) five_edits_within_30_days
  group by month
  order by month
;

/* users who signed up and made 5 edits in month X */
 select DATE_FORMAT(CONCAT(month, '01'), '%Y-%m-%d') as month,
        count(*) as active_editors
   from (select rev.rev_user as user_id,
                EXTRACT(YEAR_MONTH from rev.rev_timestamp) as month
           from enwiki.revision rev
                    inner join
                enwiki.page page    on page.page_id = rev.rev_page
          where page.page_namespace = 0
            and rev.rev_timestamp > DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 DAY), '%Y%m%d000000')
          group by rev.rev_user, month
         having count(*) >= 5
        ) active_editors
            inner join
        (select ssac.event_userId as user_id
           from log.ServerSideAccountCreation_5487345 ssac
          where ssac.wiki = 'enwiki'
            and ssac.event_displayMobile = 1
          group by ssac.event_userId
        ) accounts_created  on accounts_created.user_id = active_editors.user_id
  group by month
;


/* users who signed up in month X and made 5 edits anytime */
 select DATE_FORMAT(CONCAT(accounts_created.month, '01'), '%Y-%m-%d') as month,
        count(*) as five_time_editors
   from (select rev.rev_user as user_id
           from enwiki.revision rev
                    inner join
                enwiki.page page    on page.page_id = rev.rev_page
          where page.page_namespace = 0
            and rev.rev_timestamp > DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 DAY), '%Y%m%d000000')
          group by rev.rev_user
         having count(*) >= 5
        ) five_time_editors
            inner join
        (select ssac.event_userId as user_id,
                EXTRACT(YEAR_MONTH from ssac.timestamp) as month
           from log.ServerSideAccountCreation_5487345 ssac
          where ssac.wiki = 'enwiki'
            and ssac.event_displayMobile = 1
            and ssac.timestamp >= DATE_SUB(now(), INTERVAL 10 DAY)
        ) accounts_created      on accounts_created.user_id = five_time_editors.user_id
  group by accounts_created.month
;