Untitled query #58544

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

SHOW TABLES;

SELECT * FROM recentchanges WHERE 
rc_timestamp > '20230822000000'

/*
SELECT cl_to, COUNT(cl_from) AS antal FROM categorylinks JOIN page ON cl_from=page_id 
GROUP BY cl_to
*/
/*
SELECT cl_to, COUNT(*) AS antal FROM revision JOIN page AS p1 on rev_page=p1.page_id JOIN categorylinks ON p1.page_id=cl_from 
JOIN page AS p2 ON cl_to=p2.page_title JOIN page_props ON p2.page_id = pp_page WHERE
p1.page_namespace = 0
AND p2.page_namespace = 14
AND p1.page_is_redirect = 0
AND rev_parent_id = 0
AND rev_timestamp >= '20230630220000'
AND rev_timestamp < '20230731220000'
AND cl_to NOT IN (
SELECT cl_to FROM revision JOIN page AS p1 on rev_page=p1.page_id JOIN categorylinks ON p1.page_id=cl_from 
JOIN page AS p2 ON cl_to=p2.page_title JOIN page_props ON p2.page_id = pp_page WHERE
p1.page_namespace = 0
AND p2.page_namespace = 14
AND p1.page_is_redirect = 0
AND pp_propname LIKE 'hiddencat'
AND rev_parent_id = 0
AND rev_timestamp >= '20230630220000'
AND rev_timestamp < '20230731220000')
GROUP BY cl_to
ORDER BY antal DESC, cl_to
/*

/*
SELECT cl_to, COUNT(page_title) AS antal FROM page JOIN categorylinks ON page_id=cl_from WHERE
*/
/*
SELECT COUNT(page_title) AS antal FROM page JOIN categorylinks ON page_id=cl_from WHERE
*/
/*
page_namespace = 0
AND page_is_redirect = 1
AND cl_to NOT LIKE 'Omdirigeringar%'
AND cl_to NOT LIKE '%synonymer'
AND cl_to NOT LIKE 'Sidnamn%'

GROUP BY cl_to
ORDER BY antal DESC
*/
/*
SELECT CONCAT('[[',page_title,']]') as Artikel, CONCAT('[[:Kategori:',rd_title,']]') AS Kategori, MAX(rev_timestamp) AS senast_redigerad, COUNT(rev_timestamp) AS antal_redigeringar  FROM page JOIN redirect ON page_id = rd_from JOIN revision ON page_id=rev_page WHERE
page_namespace = 0
AND page_is_redirect = 1
AND rd_namespace = 14
GROUP BY page_title
ORDER BY senast_redigerad
*/

/*
SELECT SUBSTR(rev_timestamp,1,6) AS m , COUNT(page_title) AS antal FROM revision JOIN page on rev_page=page_id WHERE
page_namespace = 14
AND rev_parent_id = 0
GROUP BY m
ORDER BY m
*/

/*
SELECT actor_name, COUNT(*) AS antal FROM revision JOIN page ON rev_page=page_id JOIN actor on rev_actor=actor_id WHERE
rev_timestamp > '20230101000000'
AND (actor_name LIKE '%Bot' OR actor_name LIKE '%bot')
GROUP BY actor_name
ORDER BY antal DESC
*/

/*
SELECT log_timestamp AS tidpunkt, REPLACE(log_title,'_',' ') AS 'flyttad kategori', log_params AS 'flyttad till (se target)', actor_name AS utförare FROM logging JOIN actor ON log_actor=actor_id WHERE
log_namespace = 14 AND 
log_action = 'move'
ORDER BY tidpunkt DESC
LIMIT 1000
*/

/**
SELECT DISTINCT REPLACE(CONCAT('[[',page.page_title,']]'),'_',' ') AS Artikel, COUNT(cl3.cl_to) AS Antal, REPLACE(GROUP_CONCAT(CONCAT('[[:Kategori:',cl3.cl_to,']]')),'_',' ') AS Personer_från_kategorier  FROM page JOIN (
SELECT  page.page_id, COUNT(cl2.cl_to) AS antal FROM categorylinks AS cl1 JOIN page on cl_from=page_id JOIN categorylinks AS cl2 ON cl2.cl_from=page_id WHERE 
cl1.cl_to = 'Kvinnor' AND
cl2.cl_to LIKE 'Personer_från_%' 
GROUP BY page.page_id
HAVING antal>1
) AS test ON page.page_id = test.page_id JOIN categorylinks AS cl3 ON page.page_id = cl3.cl_from WHERE
cl3.cl_to LIKE 'Personer_från%'
GROUP BY Artikel, Antal
ORDER BY Artikel;
SELECT DISTINCT REPLACE(CONCAT('[[',page.page_title,']]'),'_',' ') AS Artikel, COUNT(cl3.cl_to) AS Antal, REPLACE(GROUP_CONCAT(CONCAT('[[:Kategori:',cl3.cl_to,']]')),'_',' ') AS Personer_från_kategorier  FROM page JOIN (
SELECT  page.page_id, COUNT(cl2.cl_to) AS antal FROM categorylinks AS cl1 JOIN page on cl_from=page_id JOIN categorylinks AS cl2 ON cl2.cl_from=page_id WHERE 
cl1.cl_to = 'Män' AND
cl2.cl_to LIKE 'Personer_från_%' 
GROUP BY page.page_id
HAVING antal>1
) AS test ON page.page_id = test.page_id JOIN categorylinks AS cl3 ON page.page_id = cl3.cl_from WHERE
cl3.cl_to LIKE 'Personer_från%'
GROUP BY Artikel, Antal
ORDER BY Artikel;
**/

/*
SELECT DISTINCT REPLACE(CONCAT('[[',page.page_title,']]'),'_',' ') AS Artikel, COUNT(cl3.cl_to) AS Antal, REPLACE(GROUP_CONCAT(CONCAT('[[:Kategori:',cl3.cl_to,']]')),'_',' ') AS Personer_från_kategorier  FROM page JOIN (
SELECT  DISTINCT page.page_id, COUNT(cl2.cl_to) AS antal FROM categorylinks AS cl1 JOIN page on cl_from=page_id JOIN categorylinks AS cl2 ON cl2.cl_from=page_id WHERE 
(cl1.cl_to = 'Kvinnor' OR cl1.cl_to = 'Män') AND
cl2.cl_to LIKE 'Personer_från_%' 
GROUP BY page.page_id
HAVING antal>1
) AS test ON page.page_id = test.page_id JOIN categorylinks AS cl3 ON page.page_id = cl3.cl_from WHERE
cl3.cl_to LIKE 'Personer_från%'
GROUP BY Artikel, Antal
ORDER BY Artikel;
*/

/*
JOIN categorylinks AS cl3 ON page.page_id=cl3.cl_from
*/
/*
JOIN categorylinks AS cl3 ON page.page_id=cl3.cl_from
*/
/*
SELECT  CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS Kvinna, COUNT(cl2.cl_to) AS Antal_personer_från_kategorier FROM categorylinks AS cl1 JOIN page on cl_from=page_id JOIN categorylinks AS cl2 ON cl2.cl_from=page_id WHERE 
cl1.cl_to = 'Kvinnor' AND
cl2.cl_to LIKE 'Personer_från_%' 
GROUP BY Kvinna
HAVING Antal_personer_från_kategorier>1
ORDER BY Kvinna;

SELECT  CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS Man, COUNT(cl2.cl_to) AS antal_personer_från_kategorier FROM categorylinks AS cl1 JOIN page on cl_from=page_id JOIN categorylinks AS cl2 ON cl2.cl_from=page_id WHERE 
cl1.cl_to = 'Män' AND
cl2.cl_to LIKE 'Personer_från_%' 
GROUP BY Man
HAVING Antal_personer_från_kategorier>1
ORDER BY Man;
*/
/*
SELECT pl_from_namespace AS namnrymd, Count(page2.page_title) AS antal FROM page 
*/
/*
select distinct page.page_title as kategori, page2.page_title as länkas_från, pl_from_namespace as namnrymd FROM page JOIN pagelinks ON pl_title=page.page_title JOIN page AS page2 ON pl_from=page2.page_id
AND pl_namespace=page.page_namespace
WHERE pl_namespace = 14
AND pl_from_namespace = 10
AND page.page_is_redirect
*/
/*
GROUP BY namnrymd
*/
/*
ORDER BY pl_from_namespace, page.page_title, page2.page_title
*/
/*

SELECT rc_timestamp FROM recentchanges INNER JOIN actor ON rc_actor=actor_id WHERE 
actor_name = 'Larske' AND
rc_timestamp > '20230701000000'
ORDER BY rc_timestamp
*/
/*
SELECT page_title, page_len FROM categorylinks INNER JOIN page ON cl_from=page_id WHERE
cl_to = 'Alla_stubbar'
AND page_len>=2500
AND page_len<=2577
AND page_namespace=0
ORDER BY page_len DESC
*/


/*
SELECT CONCAT('[[:Kategori:',REPLACE(cl_to,'_',' '),']]') AS Kategori,page_namespace AS Namnrymd, CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS Sida FROM categorylinks INNER JOIN page ON cl_from=page_id WHERE 
cl_to LIKE 'Artiklar_%' 
AND NOT page_namespace IN (0,14)
ORDER BY Kategori, Namnrymd, Sida
*/

/*
USE svwiki_p;
SELECT lt_title, COUNT(page_title) AS antal FROM page JOIN templatelinks ON page_id=tl_from INNER JOIN linktarget ON tl_target_id = lt_id WHERE
page_namespace = 0 AND
tl_from IS NULL AND
lt_title IN (SELECT page_title FROM categorylinks INNER JOIN page on cl_from=page_id WHERE cl_to='Mallar_som_använder_data_från_Wikidata')
GROUP BY lt_title;

/*
USE fiwiki_p;
SELECT lt_title, COUNT(page_title) AS antal FROM page INNER JOIN templatelinks ON page_id=tl_from INNER JOIN linktarget ON tl_target_id = lt_id WHERE
page_namespace = 0 AND
lt_title IN (SELECT page_title FROM categorylinks INNER JOIN page on cl_from=page_id WHERE cl_to='Wikidataa_hyödyntävät_mallineet')
GROUP BY lt_title;
*/

/*
(SELECT page_title FROM categorylinks INNER JOIN page on cl_from=page_id WHERE cl_to='Mallar_som_använder_data_från_Wikidata')

/*
SELECT page_title FROM page WHERE
page_title REGEXP '_Q[1-9]'
*/

/*
SELECT DISTINCT rc_title FROM recentchanges INNER JOIN page ON rc_title=page_title WHERE
rc_timestamp > '20221226000000'
AND rc_namespace = 0
AND rc_type < 2
ORDER BY rc_timestamp DESC
LIMIT 1000
*/

/*
SELECT MID(log_timestamp,1,6) AS blockeringsmanad, COUNT(*) FROM logging WHERE
log_type = 'block' AND
log_timestamp > '2018010100000' AND log_timestamp < '20221201000000'
GROUP BY blockeringsmanad
ORDER BY blockeringsmanad
*/

/*
SELECT * FROM user LEFT JOIN ipblocks ON user_id=ipb_user WHERE
ipb_user IS NULL AND
user_registration > '20221001000000' AND user_registration < '20221201000000' AND
user_editcount >= 5
*/
/*
GROUP BY registreringsmanad
ORDER BY registreringsmanad
*/

/*
SELECT MID(user_registration,1,6) AS registreringsmanad, COUNT(*) AS antal FROM user LEFT JOIN ipblocks ON user_id=ipb_user WHERE
ipb_user IS NULL AND
user_registration > '20170101000000' AND user_registration < '20221201000000' AND
user_editcount >= 5
GROUP BY registreringsmanad
ORDER BY registreringsmanad
*/

/*
SELECT actor_name, COUNT(*) AS antal FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor=actor_id WHERE 
page_title LIKE 'Opinionsundersökningar_inför_riksdagsvalet_i_Sverige_2022' AND
page_namespace = 0
GROUP BY actor_name
ORDER BY antal DESC
*/

/*
SELECT  page_title, MAX(rc_timestamp) AS senast, COUNT(*) AS antal FROM recentchanges INNER JOIN page ON rc_title=page_title INNER JOIN categorylinks ON page_id=cl_from WHERE
rc_timestamp > '20221101000000' AND
rc_type < 5 AND
(cl_to = 'Kvinnor' OR cl_to = 'Män')
GROUP BY page_title
*/
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...
