#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;