Untitled query #17068

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

#What databases exist?
	#SHOW DATABASES;
#Select a database
USE barwiki_p;

SELECT page_title, page_id, page_len, COUNT(DISTINCT rev_user) AS NumEditors, COUNT(rev_id) AS NumEdits 

FROM revision JOIN page ON rev_page = page_id 

WHERE LOWER(rev_user_text) NOT LIKE LOWER("%bot%") AND LOWER(rev_user_text) NOT LIKE LOWER("%bo t%") AND
rev_user NOT LIKE "0" AND
page_namespace = "0" AND
page_is_redirect = "0"

GROUP BY page_id
ORDER BY RAND()
Limit 100;


#Page Editors Pagelength


#How many times was each page edited
#SELECT COUNT(rev_page) AS rev_counts FROM revision GROUP BY rev_page LIMIT 100;

#Add where namespace = 0
#SELECT COUNT(rev_page) AS rev_counts, page_title FROM revision JOIN page ON rev_page = page_id WHERE page_namespace = "0" GROUP BY rev_page LIMIT 100;

#Add where user_name not like bot
#SELECT COUNT(rev_page) AS rev_counts, page_title FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user = user_id WHERE user_name NOT LIKE "%bot%" AND page_namespace = "0" GROUP BY rev_page LIMIT 100;

#QUESTION 1
#Shift to editors (how many distinct editors that are not bots have edited each page in namespace = 0: - filters out unregistered users
#SELECT COUNT(DISTINCT rev_user) AS usercounts, COUNT(rev_page) AS revisioncounts, page_title, page_id FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user = user_id WHERE user_name NOT LIKE "%bot%" AND user_name NOT LIKE "%BOT%" AND  user_name NOT LIKE "%Bot%" AND user_name NOT LIKE "%BoT%" AND user_name NOT LIKE "%bo T%" AND page_namespace = "0" GROUP BY rev_page ORDER BY usercounts DESC LIMIT 100;

#QUESTION 2 (from above)
#SELECT COUNT(rev_page) AS revisioncounts, page_title FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user = user_id WHERE user_name NOT LIKE "%bot%" AND user_name NOT LIKE "%BOT%" AND  user_name NOT LIKE "%Bot%" AND user_name NOT LIKE "%BoT%" AND user_name NOT LIKE "%bo T%" AND page_namespace = "0" GROUP BY rev_page ORDER BY revisioncounts DESC;

#QUESTION 3
#SELECT page_len, page_title FROM page WHERE page_namespace = "0" ORDER BY page_len DESC;

#QUESTION 4
#SELECT page_len AS talk_len, page_title FROM page WHERE page_namespace = "1" ORDER BY page_len DESC;


#Repeat without redicrect 
#QUESTION 1
#Shift to editors (how many distinct editors that are not bots have edited each page in namespace = 0: - filters out unregistered users
#SELECT COUNT(DISTINCT rev_user) AS usercounts, COUNT(rev_page) AS revisioncounts, page_title, page_id, page_len FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user = user_id WHERE user_name NOT LIKE "%bot%" AND user_name NOT LIKE "%BOT%" AND  user_name NOT LIKE "%Bot%" AND user_name NOT LIKE "%BoT%" AND user_name NOT LIKE "%bo T%" AND page_namespace = "0" AND page_is_redirect = "0" GROUP BY rev_page ORDER BY usercounts DESC Limit 100;


#QUESTION 2 (from above)
#SELECT COUNT(rev_page) AS revisioncounts, page_title FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user = user_id WHERE user_name NOT LIKE "%bot%" AND user_name NOT LIKE "%BOT%" AND  user_name NOT LIKE "%Bot%" AND user_name NOT LIKE "%BoT%" AND user_name NOT LIKE "%bo T%" AND page_namespace = "0" AND page_is_redirect = "0" GROUP BY rev_page ORDER BY revisioncounts DESC;

#QUESTION 3
#SELECT page_len, page_title FROM page WHERE page_namespace = "0" AND page_is_redirect = "0" ORDER BY page_len DESC;

#QUESTION 4
#SELECT page_len AS talk_len, page_title FROM page WHERE page_namespace = "1" AND page_is_redirect = "0"ORDER BY page_len DESC;


#Show all tables in the database
	#SHOW TABLES;
#Get info about a table
	#DESCRIBE page; 
#Select everything from a table (or some column), limit the number of entires like .head()
	#SELECT * FROM page LIMIT 10
#Order table
	#SELECT * FROM revision ORDER by rev_timestamp LIMIT 10
#Order table but descending
	#SELECT rev_page FROM revision ORDER by rev_timestamp DESC limit 10
#Join tables
	#SELECT rev_page, page_title, rev_timestamp FROM revision JOIN page ON rev_page = page_id ORDER by rev_timestamp ASC limit 10;
    
#16    
#SELECT COUNT(rev_id) AS editcounts, rev_user, user_name, user_registration FROM revision JOIN user ON user_id = rev_user WHERE user_name NOT LIKE "%bot%" AND user_name NOT LIKE "%Bot%" GROUP by rev_user DESC LIMIT 10000;

#18
#SELECT COUNT(rev_id) AS editcounts, page_title FROM revision JOIN user ON rev_user = user_id JOIN page ON page_id  = rev_page WHERE user_name="Auntof6" and page_namespace = "0" GROUP BY rev_page ORDER BY editcounts DESC LIMIT 10;
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...
