Big Data: Week 1

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

SQL

USE dewiki_p;
#0: INTRO
#SHOW TABLES;
#DESCRIBE page;
#SELECT * FROM page LIMIT 10;
#SELECT * FROM revision ORDER by rev_timestamp LIMIT 10;
#SELECT rev_page FROM revision ORDER by rev_timestamp DESC limit 10;
#SELECT rev_page, page_title, rev_timestamp FROM revision JOIN page ON rev_page = page_id ORDER by rev_timestamp ASC limit 10;
#SELECT * FROM user limit 10;
#---
#11 double JOIN
#SELECT rev_page, page_title, rev_timestamp, rev_user, user_name FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user = user_id ORDER by rev_timestamp ASC limit 10;
#==GROUP==
#12 COUNT --> failed execute command denied to user 'u2029'@'%' for routine 'dewiki_p.COUNT'
#SELECT COUNT (rev_id) FROM revision WHERE rev_user ="66249";
#13 GROUP --> failed execute command denied to user 'u2029'@'%' for routine 'dewiki_p.COUNT'
#SELECT COUNT (rev_id) FROM revision GROUP by rev_user LIMIT 10;
#14
#SELECT COUNT(rev_id) AS editcounts FROM revision GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;
#15
SELECT COUNT(rev_id) AS editcounts, rev_user, user_name FROM revision JOIN user ON user_id = rev_user GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;
#==WHERE NOT LIKE==
#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 ORDER BY editcounts DESC LIMIT 10;
#==MOST EDITED PAGE BY AUTHOR==
#17
#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" GROUP BY rev_page ORDER BY editcounts DESC LIMIT 10;
#==NAMESPACE==
#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;
#==PAGELINKS==
#19
#SELECT  pl_from, page_title FROM pagelinks JOIN page ON page_id = pl_from WHERE pl_title = "Oxford" AND pl_namespace = "0" LIMIT 10;
#==PAGELINKS FROM OXFORD==
#20
#SELECT  pl_title FROM pagelinks JOIN page ON page_id = pl_from WHERE page_title = "Oxford" AND pl_namespace = "0" and page_namespace = "0" LIMIT 10;
#==MOST LINKED PAGE
#21
#SELECT COUNT(*) AS linkscount, pl_title FROM pagelinks WHERE pl_namespace = "0" GROUP BY pl_title ORDER BY linkscount DESC LIMIT 10;
#==LARGEST NR OF SISTER ARTICLES IN OTHER LANGS==
#22
#SELECT COUNT(*) AS langcounts, ll_from, page_title FROM langlinks JOIN page on ll_from = page_id WHERE page_namespace  = 0 GROUP BY ll_from ORDER BY langcounts DESC LIMIT 10;
#==NUMBER OF USERS / NUMBER OF EDITS
#23
#SELECT COUNT(DISTINCT rev_user) AS usercounts, COUNT(rev_id) editcounts FROM revision JOIN page ON page_id = rev_page group by rev_page 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...