PT2018 - Quarry

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

PT2018

SQL

# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE bnwiki_p; # For Language Bengali
SELECT

'bn',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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'ব্যাঘ্র_প্রকল্প_এডিটাথন_২০১৮-এর_অংশ_হিসেবে_তৈরিকৃত_নিবন্ধ'
OR cl_to = 'ব্যাঘ্র_প্রকল্প_এডিটাথন_২০১৮-এর_অংশ_হিসেবে_সম্প্রসারিত_নিবন্ধ')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE enwiki_p; # For Language English
SELECT

'en', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE guwiki_p; # For Language Gujarati
SELECT

'gu', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE hiwiki_p; # For Language Hindi
SELECT

'hi', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE knwiki_p; # For Language Kannada
SELECT

'kn', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'ಪ್ರಾಜೆಕ್ಟ್_ಟೈಗರ್_೨೦೧೮_ಸಮಯದಲ್ಲಿ_ರಚಿಸಿದ_ಲೇಖನಗಳು'
OR cl_to = 'ಪ್ರಾಜೆಕ್ಟ್_ಟೈಗರ್_೨೦೧೮ನ_ಸ್ಪರ್ಧೆಯಲ್ಲಿ_ವಿಸ್ತರಿಸಲಾದ_ಲೇಖನಗಳು')))
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;
# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha

USE mlwiki_p;
# For Language Malayalam
SELECT

'ml', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = '2018ലെ_പ്രോജക്റ്റ്_ടൈഗർ_തിരുത്തൽ_യജ്ഞത്തിന്റെ_ഭാഗമായി_സൃഷ്ടിക്കപ്പെട്ട_ലേഖനങ്ങൾ'
OR cl_to = '2018ലെ_പ്രോജക്റ്റ്_ടൈഗർ_തിരുത്തൽ_യജ്ഞത്തിന്റെ_ഭാഗമായി_വികസിപ്പിക്കപ്പെട്ട_ലേഖനങ്ങൾ')))
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;

# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE mrwiki_p; # For Language Marathi
SELECT

'mr', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE orwiki_p; # For Language Oriya
SELECT

'or', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'ଟାଇଗର_ପ୍ରକଳ୍ପ_ଗଣସମ୍ପଦନା_୨୦୧୮_ଅନ୍ତର୍ଗତ_ଗଢା_ଯାଇଥିବା_ପ୍ରସଙ୍ଗ'
OR cl_to = 'ଟାଇଗର_ପ୍ରକଳ୍ପ_ଗଣସମ୍ପାଦନା_୨୦୧୮_ଅନ୍ତର୍ଗତ_ଉନ୍ନତ_କରା_ଯାଇଥିବା_ପ୍ରସଙ୍')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE pawiki_p; # For Language Punjabi
SELECT

'pa', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'ਉਹ_ਲੇਖ_ਜੋ_ਪ੍ਰੋਜੈਕਟ_ਟਾਈਗਰ_ਲੇਖ_ਲਿਖਣ_ਮੁਕਾਬਲੇ_ਅਧੀਨ_ਬਣਾਏ_ਗਏ'
OR cl_to = 'ਉਹ_ਲੇਖ_ਜੋ_ਪ੍ਰੋਜੈਕਟ_ਟਾਈਗਰ_ਲੇਖ_ਲਿਖਣ_ਮੁਕਾਬਲੇ_ਅਧੀਨ_ਵਧਾਏ_ਗਏ')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE sawiki_p; # For Language Sanskrit
SELECT

'sa', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE sdwiki_p; # For Language Sindhi
SELECT

'sd', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE tawiki_p; # For Language Tamil
SELECT

'ta', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'வேங்கைத்_திட்டம்_கட்டுரைப்_போட்டி_2018_மூலம்_உருவாக்கிய_கட்டுரைகள்'
OR cl_to = 'வேங்கைத்_திட்டம்_கட்டுரைப்_போட்டி_2018_மூலம்_விரிவாக்கிய_கட்டுரைகள்')))
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;# Quarry for extracting details about new or expanded articles created during Project Tiger Ediatathon 2018
#Creator User:ViswaPrabha
USE urwiki_p; # For Language Urdu
SELECT

'ur', 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 = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_created_as_part_of_Project_Tiger_editathon_2018'
OR cl_to = 'Articles_expanded_as_part_of_Project_Tiger_editathon_2018')))
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...