Fiwikistä 2020 merkittävyysperusteella poistetut uusien käyttäjien artikkelit

SQL

USE fiwiki_p;

SELECT 
	ar_title, 
    max_len, 
    t.log_comment as log_comment_create
FROM (
	SELECT 
  		MIN(log_id) AS t, 
  		MAX(ar_len) AS max_len, 
  		ar_title, 
  		log_comment 
  	FROM 
  		logging_compat 
  		LEFT JOIN page AS p1 ON  p1.page_id=log_page 
  		LEFT JOIN archive ON ar_title=log_title AND ar_namespace=0 
  		LEFT JOIN  user ON log_user=user_id 
  	WHERE 
  		log_namespace=0 
  		AND log_type="create" 
  		AND log_timestamp>20200000000000 
  		AND (
          	user_registration > 20190000000000 
          	OR user_editcount<1000 -- cut-off for users without user_registration timestamp
        ) 
  		AND (ar_len>300 OR p1.page_len >300) 
  	GROUP BY ar_title) AS t, 
    logging_compat AS l 
WHERE 
	log_id > 11602303 -- = shortcut for checking only log events after 20200000000000
    AND log_title=ar_title 
    AND log_namespace = 0 
    AND log_type="delete" 
    AND l.log_comment LIKE "%#A5%" 
GROUP BY ar_title;

SQL

Checking query status...