Untitled query #63768

This query is marked as a draft This query has been published by Nivas10798.
SQL

SET @Initial = "20171001000000";

WITH
cat_filter AS (
  SELECT rev_id, rev_actor
  FROM categorylinks
  INNER JOIN revision ON cl_from = rev_page
  WHERE cl_to = "Images_from_Wiki_Loves_Monuments_2017" AND cl_type = "file" AND rev_parent_id = 0),

newuser_filter AS (
  SELECT DISTINCT actor_user, actor_id
  FROM cat_filter
  INNER JOIN actor ON actor_id = rev_actor AND rev_actor != 0
  INNER JOIN user ON actor_user = user_id AND user_id != 0
  WHERE user_registration BETWEEN "20170901000000" and "20170930235959" AND user_name NOT LIKE '%Bot%'),
  
edit_filter AS (
  SELECT user_name, user_registration, user_id, rev_timestamp, rev_id
  FROM newuser_filter
  INNER JOIN user ON actor_user = user_id
  RIGHT JOIN revision ON actor_id = rev_actor AND rev_actor IS NOT NULL),

editspermonth_filter AS (
  SELECT user_name, user_registration, SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 0 AND 30) AS after_thirty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 31 AND 60) AS after_sixty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 61 AND 90) AS after_ninty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 91 AND 120) AS after_onetwenty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 121 AND 150) AS after_onefifty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 151 AND 180) AS after_oneeighty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 181 AND 210) AS after_twoten_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 211 AND 240) AS after_twofourty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 241 AND 270) AS after_twoseventy_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 271 AND 300) AS after_threehundred_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 301 AND 330) AS after_threethirty_days,
  		 SUM(DATEDIFF(rev_timestamp, @Initial) BETWEEN 331 AND 365) AS after_threesixtyfive_days
  FROM edit_filter
  WHERE user_name IS NOT NULL
  GROUP BY user_name)
  
SELECT COUNT(DISTINCT user_name) AS count_of_users,
	   CASE
            WHEN (((after_thirty_days >= 5) AND (after_sixty_days >= 5)) OR
                 ((after_sixty_days >= 5) AND (after_ninty_days >= 5)) OR
                 ((after_ninty_days >= 5) AND (after_thirty_days >= 5))) THEN '5+ in atleast 60 of first 90 days'
       END AS EPM
FROM editspermonth_filter
GROUP BY EPM

UNION

SELECT COUNT(DISTINCT user_name) AS count_of_users,
	   CASE
            WHEN (((after_thirty_days >= 5) AND (after_sixty_days >= 5) AND (after_ninty_days >= 5) AND (after_onetwenty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_sixty_days >= 5) AND (after_ninty_days >= 5) AND (after_onefifty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_sixty_days >= 5) AND (after_ninty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_sixty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_onefifty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_sixty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_sixty_days >= 5) AND (after_onefifty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_ninty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_onefifty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_ninty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_ninty_days >= 5) AND (after_onefifty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_thirty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_onefifty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_sixty_days >= 5) AND (after_ninty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_onefifty_days >= 5)) OR
                 ((after_sixty_days >= 5) AND (after_ninty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_sixty_days >= 5) AND (after_ninty_days >= 5) AND (after_onefifty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_sixty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_onefifty_days >= 5) AND (after_oneeighty_days >= 5)) OR
                 ((after_ninty_days >= 5) AND (after_onetwenty_days >= 5) AND (after_onefifty_days >= 5) AND (after_oneeighty_days >= 5))) 
                 THEN '5+ in atleast 120 of first 180 days'
                  
            #WHEN after_thirty_days BETWEEN 11 AND 1 THEN '5-10 after 30 days'
            #WHEN after_thirty_days >= 10 THEN '10+ after 30 days'
            #WHEN after_thirty_days >= 30 THEN '30+ after 30 days'
            #WHEN after_thirty_days >= 5 AND after_sixty_days >= 5 THEN 'Five EPM for next sixty days'
            #WHEN after_thirty_days >= 5 AND after_sixty_days >= 5 AND after_ninty_days >=5 THEN 'Five EPM for next ninty months'
       END AS EPM
  #IF(after_thirty_days >= 5, COUNT(DISTINCT user_name), 0) AS above_five_per_month
FROM editspermonth_filter
GROUP BY EPM
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
