SQL
x
#Most thanked editors
USE lvwiki_p;
SELECT log_title, COUNT(*)
FROM logging
WHERE log_type="thanks" and log_timestamp between 20160101000000 and 20170101000000
GROUP BY log_title
ORDER BY COUNT(*) DESC
LIMIT 100;
#Most common thanker/thanked unordered pairs
USE lvwiki_p;
SELECT LEAST(REPLACE(log_title,'_',' '),log_user_text), GREATEST(REPLACE(log_title,'_',' '), log_user_text), COUNT(*)
FROM logging
WHERE log_type="thanks" and log_timestamp between 20160101000000 and 20170101000000
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 100;
#Most thankful Latvian Wikipedia editors
USE lvwiki_p;
SELECT log_user_text, COUNT(*)
FROM logging
WHERE log_type="thanks" and log_timestamp between 20160101000000 and 20170101000000
GROUP BY log_user_text
ORDER BY COUNT(*) DESC
LIMIT 100;
#visvairāk labotie raksti
use lvwiki_p;
SELECT page_title as "Lapas nosaukums", page_len as "Lapas garums",
count(*) AS "Labojumi", count(distinct rev_user_text) as "Labotāji",
ROUND(LN(COUNT(*)) * (LN(COUNT(DISTINCT rev_user_text))+1.4), 2) AS weighted_rank
FROM revision
LEFT JOIN page ON revision.rev_page=page.page_id
WHERE page_namespace=0 and rev_timestamp between 20160101000000 and 20170101000000
GROUP BY page_title
ORDER BY count(*) DESC, count(rev_user_text) desc, page_title
LIMIT 100;
#izveidoto rakstu skaits pēc dienas
use lvwiki_p;
SELECT left(t4.rev_timestamp,8) as Menesis, count(left(t4.rev_timestamp,8)) as Raksti
FROM revision AS t4
WHERE left(t4.rev_timestamp,8) between 20150101 and 20160101 and t4.rev_id IN (
Select min(t45.rev_id)
From revision t45
inner join page p on t45.rev_page=p.page_id
where p.page_namespace=0 and p.page_is_redirect=0
group by t45.rev_page
having min(t45.rev_id)
order by min(t45.rev_id) desc
)
GROUP BY left(t4.rev_timestamp,8);
#users with the most edits (article namespace)
use lvwiki_p;
SELECT concat('* [[User:',r.rev_user_text,']]'), COUNT(*) AS num_edits
FROM revision r
join page p on p.page_id=r.rev_page
WHERE p.page_namespace=0 and r.rev_timestamp between 20160101000000 and 20170101000000
GROUP BY r.rev_user_text
ORDER BY COUNT(*) DESC
LIMIT 50;
# labojumi pēc dienas - ar botiem
use lvwiki_p;
SELECT left(t4.rev_timestamp,8) as Diena, count(left(t4.rev_timestamp,8)) as Labojumi
FROM revision AS t4
inner join page p on t4.rev_page=p.page_id
WHERE p.page_namespace=0 and p.page_is_redirect=0 and t4.rev_timestamp between 20160101000000 and 20170101000000
GROUP BY left(t4.rev_timestamp,8);
##bez botiem:
use lvwiki_p;
SELECT left(t4.rev_timestamp,8) as Diena, count(left(t4.rev_timestamp,8)) as Labojumi
FROM revision AS t4
inner join page p on t4.rev_page=p.page_id
WHERE p.page_namespace=0 and p.page_is_redirect=0 and t4.rev_timestamp between 20160101000000 and 20170101000000
and t4.rev_user not in (select ug.ug_user from user_groups ug where ug.ug_group='bot' and t4.rev_user=ug.ug_user)
GROUP BY left(t4.rev_timestamp,8);
# dalībnieki pēc izveidotajiem rakstiem
use lvwiki_p;
SELECT concat('* [[User:',r.rev_user_text,']]'), COUNT(*) AS num_articles
FROM revision r
join page p on p.page_id=r.rev_page
WHERE p.page_namespace=0 and p.page_is_redirect=0 and r.rev_timestamp between 20160101000000 and 20170101000000
and r.rev_parent_id=0
GROUP BY r.rev_user_text
ORDER BY COUNT(*) DESC
LIMIT 500;
# dalībnieki pēc laboto rakstu skaita
use lvwiki_p;
SELECT concat('* [[User:',r.rev_user_text,']]'), COUNT(distinct rev_page) AS num_edits
FROM revision r
join page p on p.page_id=r.rev_page
WHERE p.page_namespace=0 and r.rev_timestamp between 20160101000000 and 20170101000000
GROUP BY r.rev_user_text
ORDER BY COUNT(distinct rev_page) DESC
LIMIT 150;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.