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),
  
edit_cat_filter AS (
	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'
       	ELSE NULL
       	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'
       	ELSE NULL
       	END AS EPM
  	FROM editspermonth_filter
	GROUP BY EPM	
  /*
  	UNION      
	
  	SELECT COUNT(DISTINCT user_name) AS count_of_users,
	   	CASE
       			WHEN (after_thirty_days AND after_sixty_days AND after_ninty_days AND 
           		after_onetwenty_days AND after_onefifty_days AND after_oneeighty_days AND 
            	after_twoten_days AND after_twofourty_days AND after_twoseventy_days AND 
            	after_threehundred_days AND after_threethirty_days AND after_threesixtyfive_days) = 0 THEN 'No edits after end of campaign'
            	WHEN (after_thirty_days OR after_sixty_days OR after_ninty_days OR 
            	after_onetwenty_days OR after_onefifty_days OR after_oneeighty_days OR 
            	after_twoten_days OR after_twofourty_days OR after_twoseventy_days OR 
            	after_threehundred_days OR after_threethirty_days OR after_threesixtyfive_days) BETWEEN 1 AND 4 THEN 'Less than 5 edits after 30 days'
            	WHEN (after_thirty_days OR after_sixty_days OR after_ninty_days OR 
            	after_onetwenty_days OR after_onefifty_days OR after_oneeighty_days OR 
            	after_twoten_days OR after_twofourty_days OR after_twoseventy_days OR 
            	after_threehundred_days OR after_threethirty_days OR after_threesixtyfive_days) BETWEEN 5 AND 9 THEN 'Atleast 5 after 30 days'
            	WHEN (after_thirty_days OR after_sixty_days OR after_ninty_days OR 
            	after_onetwenty_days OR after_onefifty_days OR after_oneeighty_days OR 
            	after_twoten_days OR after_twofourty_days OR after_twoseventy_days OR 
            	after_threehundred_days OR after_threethirty_days OR after_threesixtyfive_days) BETWEEN 10 AND 29 THEN 'Atleast 10 after 30 days'
            	WHEN (after_thirty_days OR after_sixty_days OR after_ninty_days OR 
            	after_onetwenty_days OR after_onefifty_days OR after_oneeighty_days OR 
            	after_twoten_days OR after_twofourty_days OR after_twoseventy_days OR 
            	after_threehundred_days OR after_threethirty_days OR after_threesixtyfive_days) >= 30 THEN 'Atleast 30 after 30 days'
       	ELSE NULL
  		END AS EPM
  	FROM editspermonth_filter
	GROUP BY EPM*/
)

SELECT * 
FROM edit_cat_filter
WHERE EPM IS NOT NULL
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...
