Untitled query #17569

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

USE wikidatawiki_p;

SELECT ctd_name, COUNT(*) AS antal FROM recentchanges INNER JOIN change_tag ON rc_id=ct_rc_id INNER JOIN change_tag_def ON ct_tag_id=ctd_id WHERE
rc_timestamp > '20210101000000'
AND NOT (ctd_name LIKE 'OAuth%')
GROUP BY ctd_name
ORDER BY antal DESC

/*
EXCEPT
*/
/*
(SELECT page_title, page_namespace FROM revision INNER_JOIN page ON rev_page = page_id WHERE
 rev_timestamp > '20170701000000' AND
 page_namespace = 1 AND
 rev_actor <> 560
 LIMIT 10
)
*/

/*
SELECT rev_timestamp, actor_name, page_title, page_namespace FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor=actor_id
WHERE page_namespace IN (10,11,828,829)
AND rev_timestamp > '20200415000000' AND rev_timestamp < '20210415000000'
ORDER BY rev_timestamp
*/

/*
SELECT COUNT(DISTINCT page_title) AS antal, actor_name FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor = actor_id 
WHERE (page_namespace = 10) 
AND rev_timestamp > '20200415000000' AND rev_timestamp < '20210415000000'
GROUP BY actor_name
ORDER BY antal DESC;

SELECT COUNT(DISTINCT page_title) AS antal, actor_name FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor = actor_id 
WHERE (page_namespace = 11) 
AND rev_timestamp > '20200415000000' AND rev_timestamp < '20210415000000'
GROUP BY actor_name
ORDER BY antal DESC;

SELECT COUNT(DISTINCT page_title) AS antal, actor_name FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor = actor_id 
WHERE (page_namespace = 828) 
AND rev_timestamp > '20200415000000' AND rev_timestamp < '20210415000000'
GROUP BY actor_name
ORDER BY antal DESC;

SELECT COUNT(DISTINCT page_title) AS antal, actor_name FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor = actor_id 
WHERE (page_namespace = 829) 
AND rev_timestamp > '20200415000000' AND rev_timestamp < '20210415000000'
GROUP BY actor_name
ORDER BY antal DESC;

SELECT actor_name, SUM(IF(page_namespace=10,1,0)) AS antalmall, SUM(IF(page_namespace=11,1,0)) AS antalmalldisk, SUM(IF(page_namespace=828,1,0)) AS antalmodul, SUM(IF(page_namespace=829,1,0)) AS antalmoduldisk FROM revision INNER JOIN page ON rev_page=page_id INNER JOIN actor ON rev_actor = actor_id 
WHERE (page_namespace = 10) OR (page_namespace = 11) OR (page_namespace = 828) OR (page_namespace = 829) 
AND rev_timestamp > '20200415000000' AND rev_timestamp < '20210415000000'
GROUP BY actor_name
ORDER BY antalmodul DESC, antalmall DESC
*/

/*
SELECT * FROM recentchanges INNER JOIN actor ON rc_actor = actor_id INNER JOIN comment ON rc_comment_id=comment_id WHERE 
actor_name = 'EmausBot'
AND comment_text NOT LIKE 'Bot: Rättar dubbel omdirigering%'
LIMIT 100
*/

/*
SELECT SUBSTR(rc_timestamp,9,2) AS timme, COUNT(*) FROM recentchanges INNER JOIN actor ON rc_actor = actor_id WHERE 
actor_name = 'EmausBot'
AND rc_timestamp LIKE '20210320%'
GROUP BY timme
ORDER BY timme
*/

/*
SELECT CONCAT(YEAR(log_timestamp),'-',MONTH(log_timestamp),'-',DAY(log_timestamp)) AS dag, COUNT(*) FROM logging WHERE 
log_type = 'newusers'
AND log_timestamp > '20200101000000'
GROUP BY dag 
ORDER BY dag DESC
*/

/*
SELECT CURRENT_TIMESTAMP;

SELECT page_namespace AS Namnrymd, COUNT(*) AS Antal_sidor, FLOOR(SUM(1-page_is_redirect)) AS Varav_ej_omdirigeringar, FLOOR(SUM(page_is_redirect)) AS Varav_omdirigeringar FROM page
GROUP BY page_namespace;

SELECT CURRENT_TIMESTAMP;
*/

/*
SELECT CONCAT('[[',page_title,']]') AS artikel, page_len AS sidlängd FROM page 
WHERE page_is_redirect = 1
AND page_namespace = 0
AND page_len>500
ORDER BY page_len DESC
*/

/*
SELECT rc_timestamp, rc_title, actor_name FROM recentchanges INNER JOIN actor on rc_actor = actor_id
WHERE actor_user IS NULL 
AND rc_type < 5
*

/*
SELECT log_title, log_timestamp FROM logging WHERE log_action = 'delete' AND log_title LIKE '%(ö_i%'
*/

/*
SELECT CONCAT('[[',log_title,']]') AS artikel, log_timestamp AS raderingstid, actor_name AS raderad_av FROM logging INNER JOIN actor ON log_actor=actor_id WHERE
log_action = 'delete'
AND log_title LIKE '%(ö_i%'
AND NOT actor_name LIKE 'Lsj%'
ORDER BY log_timestamp DESC
*/

/*
SELECT * FROM recentchanges WHERE
rc_timestamp > '20210204060000'
ORDER BY rc_timestamp DESC
LIMIT 1000
*/

/*
SELECT SUBSTRING('JanFebMarAprMajJunJulAugSepOktNovDec',SUBSTRING(log_timestamp,5,2)*3-2,3) AS månad, COUNT(*)/16 AS antal_blockeringar FROM logging WHERE
log_type = 'block'
AND log_timestamp > '20050101000000'
AND log_timestamp < '20220101000000'
GROUP BY månad
ORDER BY SUBSTRING(log_timestamp,5,2)
*/

/*
SELECT log_type, SUBSTRING(log_timestamp,1,8) AS dag, COUNT(*) AS antal FROM logging INNER JOIN actor on log_actor=actor_id WHERE
log_timestamp >= '20210104000000' AND log_timestamp < '20210303000000'
AND (log_type = 'create' OR log_type = 'delete')
AND log_namespace = 0
GROUP BY log_type, dag
ORDER BY dag
*/

/*
SELECT * FROM logging WHERE
log_timestamp >= '20210104000000' AND log_timestamp < '20210307000000'
AND log_type = 'delete'
AND log_namespace = 0
*/
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...
