Untitled query #58544

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

SELECT DISTINCT pl_title, pl_from, page_title FROM pagelinks LEFT JOIN page on pl_title=page_title
WHERE page_title IS NULL
LIMIT 10000

/*
SELECT CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS Artikel, rev_timestamp AS Tidpunkt, comment_text AS Redigeringskommentar, actor_name AS Redigerare, page_namespace from revision INNER JOIN comment ON rev_comment_id=comment_id INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor=actor_id WHERE
LOWER(comment_text) LIKE '%store norske%' OR comment_text LIKE '%SNL%'
AND NOT (page_namespace = 0)
*/

/*
SELECT CONCAT('[[',REPLACE(rc_title,'_',' '),']]') AS Artikel, COUNT(*) AS Redigeringar, MAX(rc_timestamp) AS Senaste, SUM(comment_text LIKE '%Wikipedia:Tillbakarullning%') AS Tillbakarullningar, (SUM(comment_text LIKE '%Wikipedia:Tillbakarullning%')/COUNT(*)) AS Kvot FROM recentchanges INNER JOIN comment on rc_comment_id=comment_id WHERE
rc_timestamp > '20210322000000'
AND rc_namespace = 0
AND rc_bot = 0
AND rc_type < 5
/*
AND comment_text LIKE '%Wikipedia:Tillbakarullning%'
*/
/*GROUP BY rc_title
HAVING tillbakarullningar >= 3
ORDER BY kvot DESC, tillbakarullningar DESC, artikel

/*
SELECT CONCAT('Användare:', log_title) AS Från, page_title AS Till, actor_name AS Vem, log_timestamp AS När, page_len AS Storlek
FROM logging
LEFT JOIN page ON log_page=page_id
LEFT JOIN actor on log_actor=actor_id
WHERE log_type = 'move'
AND log_namespace = 2
AND page_namespace = 0
AND log_timestamp between '20220201000000' and '20220228235959'
AND page_len < 2000
ORDER BY log_timestamp DESC
*/

/*
SELECT * FROM logging INNER JOIN actor on log_actor=actor_id INNER JOIN page ON log_title=page_title 
WHERE log_timestamp > '20220101000000'
AND log_type = 'move'
AND log_namespace = 2
AND page_namespace = 0
#LIMIT 100
*/

/*
SELECT rc_actor, actor_name, COUNT(*) FROM recentchanges INNER JOIN actor ON rc_actor=actor_id WHERE 
rc_namespace = 0
AND rc_type < 5
AND rc_type = 1
AND rc_bot = 0
AND NOT actor_user IS NULL
AND rc_timestamp > '20220207000000'
GROUP BY rc_actor, actor_name
/*

/*SELECT page_title AS Wikidataobjekt, rev_timestamp AS "Tidpunkt (UTC)", actor_name AS Redigerare FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor on rev_actor=actor_id WHERE 
rev_timestamp > '20220303030400' 
AND rev_timestamp < '20220303130500'
AND page_title = 'Q921813'
*/

/*
SELECT log_timestamp, log_title, actor_name FROM logging INNER JOIN actor ON log_actor=actor_id WHERE 
log_timestamp > '20191102000000'
AND log_action = 'delete'
AND log_namespace = 0
AND NOT actor_name = 'Kitayama'
AND NOT actor_name = 'EstrellaSuecia'
AND log_title LIKE '%\_%'
#AND log_title LIKE '%spelare)'
LIMIT 200
*/
/*
SELECT count, COUNT(cl_to) AS antal FROM (SELECT cl_to, COUNT(*) AS count FROM categorylinks 
WHERE cl_to LIKE 'Personer_%'
AND cl_type = 'page'
GROUP BY cl_to) a
GROUP BY count
HAVING count<5
*/

/*
SELECT LEFT(log_timestamp,4) AS år, actor_name, COUNT(log_title) FROM logging INNER JOIN page ON log_title=page_title INNER JOIN categorylinks on page_id=cl_from INNER JOIN actor ON log_actor=actor_id WHERE
log_action = 'create'
AND log_namespace = 0
AND cl_to = 'Svenska_präster_under_1800-talet'
GROUP BY år, actor_name
*/

/*
SELECT actor_name AS användare, COUNT(*) AS antal_biografiredigeringar, COUNT(IF(cl_to='Kvinnor','1',NULL)) AS därav_antal_kvinnor, COUNT(IF(cl_to='Män','1',NULL)) AS därav_antal_män,  SUM(IF(cl_to='Kvinnor',1,0))/COUNT(*) AS andel_kvinnor, MIN(rc_timestamp) AS från, MAX(rc_timestamp) AS till FROM recentchanges INNER JOIN actor ON rc_actor=actor_id INNER JOIN page ON rc_title=page_title INNER JOIN categorylinks ON page_id=cl_from WHERE
rc_namespace = 0
AND rc_bot = 0
AND NOT rc_source = 'wb'
AND (cl_to = 'Kvinnor' OR cl_to = 'Män')
GROUP BY actor_name
HAVING antal_biografiredigeringar>=30
ORDER BY antal_biografiredigeringar DESC, andel_kvinnor DESC
*/

/* SELECT CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS Artikel, COUNT(*) AS Antal_redigeringar_under_2021, COUNT(DISTINCT rev_actor) AS Antal_redigerare_under_2021 FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor on rev_actor=actor_id WHERE 
rev_timestamp > '20210101000000' AND rev_timestamp < '20220101000000'
AND page_namespace=0
GROUP BY page_title, page_namespace
ORDER BY Antal_redigeringar_under_2021 DESC, page_title
LIMIT 1000
*/
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...
