SELECT rev_timestamp, actor_name, page_title from revision JOIN page on rev_page=page_id JOIN actor ON rev_actor=actor_id WHERE

page_namespace = 3

AND rev_parent_id = 0

/*

AND actor_name = 'Bruno Rosta'

*/

AND rev_timestamp > '20240501000000'

​

/*

SELECT actor_name, COUNT(*) AS antal FROM revision JOIN page ON rev_page=page_id JOIN categorylinks on page_id=cl_from JOIN actor ON rev_actor=actor_id WHERE

rev_timestamp >= '20221005000000'

AND cl_to = 'Sveriges_kommuner'

AND (page_title LIKE '%_kommun' OR page_title = 'Region_Gotland')

GROUP BY actor_name

ORDER BY antal DESC

*/

​

/*

SELECT  SUBSTR(rc_timestamp,1,8) AS datum, COUNT(*) AS antal FROM recentchanges JOIN change_tag ON rc_id=ct_rc_id JOIN change_tag_def ON ct_tag_id=ctd_id WHERE

ctd_name = 'tidlöshet'

GROUP BY datum

ORDER BY datum

*/

​

/*

SELECT rc_timestamp, rc_title, comment_text FROM recentchanges JOIN comment ON rc_comment_id=comment_id JOIN actor ON rc_actor=actor_id WHERE

actor_name='Kyllo'

AND

comment_text LIKE '%mallfix eftersom personen är död%'

ORDER BY rc_timestamp DESC

*/

/*

SELECT tl_from_namespace, COUNT(tl_target_id) AS antal FROM templatelinks JOIN linktarget ON tl_target_id=lt_id WHERE

lt_title = 'Wikidatalista'

AND lt_namespace = 10

GROUP BY tl_from_namespace

*/

​

/*

SELECT SUBSTR(rev_timestamp,1,8) AS datum, COUNT(SUBSTR(rev_timestamp,1,8)) AS antal, MIN(rev_timestamp) AS forsta, MAX(rev_timestamp) AS senaste FROM revision JOIN page ON rev_page=page_id JOIN actor ON rev_actor=actor_id WHERE

actor_name = 'ListeriaBot'

AND rev_timestamp>'20250101000000'

GROUP BY datum

ORDER BY datum

*/

​

/*

SELECT * FROM recentchanges JOIN change_tag ON rc_id=ct_rc_id JOIN change_tag_def ON ct_tag_id=ctd_id

WHERE 

rc_id IN ( SELECT rc_id, COUNT(*) AS antal FROM recentchanges JOIN change_tag ON rc_id=ct_rc_id GROUP BY rc_id)

AND rc_timestamp > '20250314000000'

*/

/*

​

SELECT rc_id, rc_title, rc_timestamp, COUNT(*) AS antal FROM recentchanges JOIN change_tag ON rc_id=ct_rc_id

WHERE rc_timestamp > '20250313010000'

GROUP BY rc_id, rc_title, rc_timestamp 

HAVING antal>1

*/

​

/*SELECT actor_name, COUNT(*) AS antal FROM recentchanges JOIN actor ON rc_actor=actor_id 

GROUP BY actor_name

ORDER BY antal DESC

*/

​

/*

SELECT ctd_name, SUM(IF(actor_user IS NULL,1,0)) AS antal_som_oinloggade, SUM(IF(actor_user IS NULL,0,1)) AS antal_som_inloggade FROM recentchanges JOIN actor ON rc_actor=actor_id JOIN change_tag ON rc_id = ct_rc_id JOIN change_tag_def ON ct_tag_id=ctd_id 

GROUP BY ctd_name

ORDER BY ctd_name

*/

​

/*

SELECT REPLACE(page_title,'_',' ') AS artikel, CONCAT('Kategori:',REPLACE(rd_title,'_',' ')) AS omdirigerad_till FROM page INNER JOIN redirect ON page_id = rd_from WHERE

page_namespace = 0 AND

rd_namespace = 14

ORDER BY artikel

*/

​

/*

SELECT * FROM revision JOIN actor ON rev_actor=actor_id JOIN page on rev_page=page_id WHERE

rev_timestamp > '20200512000000'

AND rev_timestamp < '20200713000000'

AND page_namespace IN (1,3,5,7,9,11,13,15)

AND actor_name LIKE '%-%'

*/

/*

SELECT actor_name, COUNT(*) AS antal_redigeringar, SUM(IF(page_namespace%2=1 OR page_title='Bybrunnen' OR page_title='Kommentarer_om_administrationen_av_Wikipedia',1,0))  AS antal_disk, SUM(IF(page_namespace % 2=1 OR page_title='Bybrunnen' OR page_title='Kommentarer_om_administrationen_av_Wikipedia',1,0))/COUNT(*) AS andel_diskussion FROM revision JOIN page ON rev_page=page_id JOIN actor ON rev_actor=actor_id WHERE

rev_timestamp > '20240915000000' 

AND rev_timestamp < '20241215000000'

AND actor_name != 'MediaWiki message delivery'

AND actor_name != 'EnBotEn'

AND actor_name NOT LIKE '%Bot'

AND actor_name NOT LIKE '%bot'

AND actor_name != 'Perfect'

GROUP BY actor_name

HAVING antal_redigeringar>99 AND antal_disk>0

ORDER BY andel_diskussion DESC

*/

/*

SELECT CONCAT(IF(page_namespace IN (0,1),'[[',''),REPLACE(page_title,'_',' '),IF(page_namespace IN (0,1),']]','')) AS titel,

SUM(IF(page_namespace=0,1,0)) AS artikelredigeringar, 

SUM(IF(page_namespace=1,1,0)) AS artikeldisk, 

SUM(IF(page_namespace=3,1,0)) AS användardisk, 

COUNT(*) AS totalt, 

MIN(rev_timestamp) AS tidigaste, 

MAX(rev_timestamp) AS senaste, 

DATEDIFF(MAX(rev_timestamp),MIN(rev_timestamp)) AS dagar_mellan_tidigaste_och_senaste 

FROM revision JOIN page ON rev_page=page_id JOIN actor ON rev_actor=actor_id WHERE 

actor_name = 'FWIlkens'

/*

AND page_namespace IN (0,1,3)

*/

/*

AND rev_timestamp > '20231201000000'

AND rev_timestamp < '20241201000000'

GROUP BY page_title

ORDER BY totalt DESC, artikelredigeringar DESC, artikeldisk DESC, användardisk DESC, titel

*/