PE2016-Generic

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

This query is written for Punjab Editathon 2016

SQL

# Quarry for extracting details about new articles created during Punjab Editathone 2016 as part of
# Wikimedia India Conference 2016 held at Chandigarh 5,6,7 August 2016
#Creator User:ViswaPrabha

#for Language en

#USE bnwiki_p; # bn Bengali
#USE enwiki_p; # en English
#USE hiwiki_p; # hi Hindi
#USE knwiki_p; # kn Kannada
#USE mlwiki_p; # ml Malayalam
#USE mrwiki_p; # mr Marathi
#USE orwiki_p; # or Oriya
#USE pawiki_p; # pa Punjabi
#USE sawiki_p; # sa Sanskrit
#USE sdwiki_p; # sd Sindhi
#USE tawiki_p; # ta Tamil
#USE tewiki_p; # te Telugu
#USE urwiki_p; # ur Urdu


SELECT

t.page_id AS PID,
r2.rev_id AS CreateRID,
r2.rev_timestamp AS CreateTime,
r2.rev_user_text AS Creator,
r1.rev_id AS LastRID,
r1.rev_timestamp AS LastTime,
r1.rev_user_text AS LastEditor,
r1.rev_len AS LastSize,
t.cnt AS Edits,
t.page_title AS Title


FROM ( 	
  	SELECT COUNT(*) AS cnt, r.rev_timestamp, p.page_id, p.page_title, p.page_latest, p.page_namespace
  	FROM revision r
	LEFT JOIN page p ON p.page_id = r.rev_page
	WHERE r.rev_page IN ( 
		SELECT page_id FROM page p2
    	WHERE p2.page_namespace = 0 AND p2.page_title IN (
		SELECT page_title FROM page
      	WHERE page_id IN ( 
			SELECT cl_from FROM categorylinks
      		WHERE
	cl_to = 'পাঞ্জাব_এডিটাথন_২০১৬-এ_তৈরিকৃত_নিবন্ধ'												#bn
OR	cl_to = 'Articles_created_as_part_of_Punjab_Editathon_-_2016'								#en
OR	cl_to = 'Articles_created_as_part_of_Punjab_Editathon_-_2016' 								#hi
OR	cl_to = "ಪಂಜಾಬ್_ಸಂಪಾದನೋತ್ಸವ_೨೦೧೬_ರ_ಅಂಗವಾಗಿ_ಸೃಷ್ಟಿಸಿದ_ಲೇಖನ"									#kn
OR	cl_to = "2016-ലെ_പഞ്ചാബ്_തിരുത്തൽ_യജ്ഞത്തിന്റെ_ഭാഗമായി_സൃഷ്ടിക്കപ്പെട്ട_ലേഖനങ്ങൾ" 			#ml
OR	cl_to = 'Articles_created_as_part_of_Punjab_Editathon_-_2016' 								#mr
OR	cl_to = 'Articles_created_as_part_of_Punjab_Editathon_-_2016' 								#or
OR	cl_to = 'ਪੰਜਾਬ_ਐਡਿਟਾਥਾਨ_2016_ਦੌਰਾਨ_ਬਣਾਏ_ਗਏ_ਲੇਖ' 												#pa
OR	cl_to = 'Articles_created_as_part_of_Punjab_Editathon_-_2016' 								#sa
OR	cl_to = 'Articles_created_as_part_of_Punjab_Editathon_-_2016' 								#sd
OR	cl_to = 'பஞ்சாப்_மாதம்_2016_தொடர்_தொகுப்பின்_போது_உருவாக்கப்பட்ட_கட்டுரைகள்'	#ta
OR	cl_to = 'ఇంకా_విలువకట్టని_పంజాబ్_ఎడిటథాన్_వ్యాసాలు' 												#te
OR	cl_to = 'تخلیق_کردہ_مضامین_بسلسلہ_پنجاب_ترمیمی_دوڑ_2016ء' 								#ur
        	) 
    	)
	)
	AND p.page_namespace = 0
	GROUP BY page_namespace, page_title
) AS t
LEFT JOIN revision r2 ON r2.rev_page = t.page_id AND r2.rev_parent_id = 0
LEFT JOIN revision r1 ON r1.rev_id = t.page_latest
GROUP BY t.page_namespace, t.page_title;

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...