Untitled query #17569

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

USE svwiki_p;

/*
SELECT CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS sida FROM page INNER JOIN categorylinks ON page_id=cl_from WHERE
  (cl_to='Kvinnor' OR cl_to='Män') AND
  page_title RLIKE '[A-HJ-Z]+_' AND
  page_namespace=0 AND
  NOT page_title RLIKE '_av_' AND
  NOT page_title RLIKE '(DJ|LL|MF|MC|SHY|VD|CC|CK|CM|CY|GG|ICS|KOGA|JR|påve|kejsare|sång|rapp|artist|twigs|A_Guy|Burnett|Langston|V_Pala|Ptolemaios|Thant|IX|XI|XX|IV|X_Gustav)' AND
  page_is_redirect = 0
ORDER BY page_title
*/

/*
SELECT page_title, pr_type, pr_level FROM page_restrictions INNER JOIN page on pr_page=page_id
WHERE page_namespace=10
AND pr_type='edit'
ORDER BY page_title
*/

/*
SELECT * FROM externallinks INNER JOIN page on el_from=page_id WHERE
el_to='https://www.youtube.com/watch?v=o-PPFgvBPxA'
*/

/*
SELECT COUNT(*) as antal, NOW() AS tid FROM externallinks WHERE
el_to='https://www.youtube.com/watch?v=o-PPFgvBPxA';

SELECT COUNT(*) as antal, NOW() AS tid FROM externallinks WHERE
el_to='https://www.nordfront.se/'
*/

SELECT pl_title, p2.page_latest FROM pagelinks INNER JOIN page AS p1 ON pl_from=page_id LEFT OUTER JOIN page AS p2 ON pl_title=p2.page_title WHERE
p1.page_title='Lista_över_naturreservat_i_Uppsala_län' AND p2.page_latest IS NULL

/*
SELECT rev_user_text AS user, COUNT(rev_id) AS num_edits
FROM revision_userindex
JOIN page ON page_id = rev_page
WHERE page_namespace = 8
AND page_title RLIKE '^.*\.(css|js)$'
AND rev_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY user
ORDER BY num_edits DESC
*/

/*
USE wikidatawiki_p;
SELECT rc_user_text, COUNT(*) AS antal FROM recentchanges WHERE
rc_new=1 AND rc_bot=1
GROUP BY rc_user_text
ORDER BY antal DESC
*/

/*
SELECT * FROM recentchanges WHERE
rc_type<5 AND rc_namespace=10
ORDER BY rc_timestamp
*/

/*
SELECT COUNT(*), SUM(IF(rc_user=0,0,1)) AS inloggad, SUM(IF(rc_user=0,IF(rc_user_text LIKE '%:%',0,1),0)) AS ipv4, SUM(IF(rc_user=0,IF(rc_user_text LIKE '%:%',1,0),0)) AS ipv6  FROM recentchanges 
WHERE rc_type<5  AND rc_bot=0 AND rc_timestamp>'20180701000000' AND rc_timestamp<'20180728000000'
*/

/*
SELECT rc_user_text, COUNT(*) AS antal FROM recentchanges
WHERE rc_type<5 AND rc_bot=0 AND rc_timestamp>'20180701000000' AND rc_timestamp<'20180728000000' AND NOT(rc_user=0)
GROUP BY rc_user_text
ORDER BY antal DESC
*/
/*
SELECT rc_user_text, COUNT(*) AS antal FROM recentchanges
WHERE rc_type<5 AND rc_bot=0 AND rc_timestamp>'20180701000000' AND rc_timestamp<'20180728000000' AND rc_user=0 AND NOT rc_user_text LIKE '%:%'
GROUP BY rc_user_text
ORDER BY antal DESC
*/
/*
SELECT rc_user_text, COUNT(*) AS antal FROM recentchanges
WHERE rc_type<5 AND rc_bot=0 AND rc_timestamp>'20180701000000' AND rc_timestamp<'20180728000000' AND rc_user=0 AND rc_user_text LIKE '%:%'
GROUP BY rc_user_text
ORDER BY antal DESC
*/
/*
SELECT page_title, el_to FROM externallinks INNER JOIN page ON el_from=page_id WHERE
(el_index LIKE 'https://se.lansstyrelsen.www%' OR el_index LIKE 'http://se.lansstyrelsen.www%') AND page_namespace=0
ORDER BY page_title
*/

/*
USE wikidatawiki_p;
SELECT * FROM wb_terms 
WHERE term_entity_type='property' AND (term_language='sv' OR term_language='en') AND term_type='label'
*/

/*
USE itwiki_p;
SELECT SUBSTR(rc_timestamp,1,10) AS klocka, COUNT(*) FROM recentchanges
WHERE rc_namespace=0 AND rc_type<5
GROUP BY klocka
ORDER BY klocka 
*/

/*
SELECT CONCAT('[[',page_title,']]') AS artikel FROM page WHERE 
page_id IN (
SELECT tl_from FROM templatelinks
WHERE tl_from_namespace=0
GROUP BY tl_from
  HAVING COUNT(tl_title) >400)
*/

/*
SELECT tl_title, tl_namespace, page_title, page_namespace FROM templatelinks INNER JOIN page ON tl_from=page_id
WHERE tl_title='Landsdata_Grönland' AND NOT page_namespace=0
LIMIT 10000
*/
/*
SELECT SUBSTR(rc_timestamp,1,8) AS datum,  COUNT(*) AS antal  FROM recentchanges
WHERE rc_namespace=1 
AND rc_user_text='InternetArchiveBot'
GROUP BY datum
ORDER BY datum
*/

/*
SELECT CONCAT('[[:Kategori:',cl_to,']]') AS Kategori,  CONCAT(IF(page_namespace=2,'[[Användare:','[[Användardiskussion:'),page_title,']]') AS Sida FROM page INNER JOIN categorylinks ON page_id=cl_from WHERE
cl_to IN (SELECT cl_to FROM categorylinks INNER JOIN page on cl_from=page_id WHERE
cl_to IN (SELECT cl_to FROM categorylinks INNER JOIN page ON cl_from=page_id WHERE 
          (page_namespace=2 OR page_namespace=3) AND NOT cl_to LIKE 'Robotidentifierade%' ) AND page_namespace=0
GROUP BY cl_to)
AND ( page_namespace=2 OR page_namespace=3 )
ORDER BY cl_to
*/

/*
SELECT CONCAT('[[:Kategori:',cl_to,']]') AS Kategori, IF(page_namespace=2,'Användare:','Användardiskussion:') AS namnrymd, 
CONCAT('[[',IF(page_namespace=2,'Användare:','Användardiskussion:'),page_title,']]') AS sidnamn FROM categorylinks INNER JOIN page on cl_from=page_id 
WHERE (page_namespace=2 OR page_namespace=3) AND NOT cl_to LIKE 'Användar%' AND NOT page_title LIKE 'Lsjbotq%' AND NOT cl_to LIKE 'Robot%' 
AND NOT cl_to LIKE 'Wiki%' AND NOT cl_to LIKE 'Sidor%' 
AND NOT cl_to LIKE 'Artiklar%' AND NOT cl_to LIKE 'WP%' AND NOT cl_to LIKE 'Projekt%' AND NOT cl_to LIKE 'Offentliga_datorer' 
AND NOT cl_to LIKE 'Skoldatorer' AND NOT cl_to LIKE 'Öppna_proxyservrar' AND NOT cl_to LIKE 'Skrivstuga%' AND NOT cl_to LIKE 'SVA-staff'
ORDER BY cl_to, page_title
*/

/*
SELECT * FROM ipblocks WHERE
ipb_expiry != 'infinity'
*/

/* 
WHERE log_type='block' AND log_params LIKE '%06-15%'
ORDER BY log_timestamp DESC
*/ 

/*
SELECT log_title, log_user, user_name, log_timestamp FROM logging INNER JOIN user ON log_user=user_id WHERE
log_action = 'block'
AND log_timestamp > '20180101000000'
#AND LENGTH(log_title)>30
AND SUBSTR(log_title,5,1)=':'
#log_timestamp > '20180518000000'
ORDER BY log_timestamp DESC
#GROUP BY log_title
#ORDER BY log_title
*/

/*
#SELECT WEEKDAY(SUBSTR(rc_timestamp,1,8)) AS veckodag, SUBSTR(rc_timestamp,9,2) AS timme, COUNT(*) AS totalt, SUM(IF(rc_user=0,IF(LENGTH(rc_user_text)<17,1,0),0)) AS IPv4, SUM(IF(rc_user=0,IF(LENGTH(rc_user_text)>16,1,0),0)) AS IPv6, SUM(IF(rc_user=0,0,1)) AS inloggad FROM recentchanges
SELECT rc_user_text, COUNT(*) AS totalt, SUM(IF(rc_user=0,IF(LENGTH(rc_user_text)<17,1,0),0)) AS IPv4, SUM(IF(rc_user=0,IF(LENGTH(rc_user_text)>16,1,0),0)) AS IPv6, SUM(IF(rc_user=0,0,1)) AS inloggad FROM recentchanges
WHERE rc_namespace = 0 AND rc_type < 5 AND rc_timestamp<'2018051800000' AND rc_timestamp>'20180420000000' AND LENGTH(rc_user_text)>16 AND rc_user=0
#GROUP BY veckodag, timme
#ORDER BY veckodag
#GROUP BY rc_user_text
*/

/*
SELECT CONCAT('[[',replace(page_title,'_',' '),']]') AS sida FROM page INNER JOIN categorylinks ON page_id=cl_from
WHERE (cl_to='Män' OR cl_to='Kvinnor') AND (page_title RLIKE '^[A-ZÅÄÖ]_' OR page_title RLIKE '_[A-ZÅÄÖ]_') AND  page_title NOT RLIKE '_[IVX]_'
ORDER BY page_title
LIMIT 2000
*/

/*
SELECT user_name, COUNT(*) AS antal FROM logging INNER JOIN user on log_user=user_id WHERE
log_action = 'move' AND log_namespace=0 AND log_timestamp>'20180201000000'
GROUP BY log_user
ORDER BY antal DESC
*/

/*
SELECT rc_user_text, COUNT(rc_id) AS antal FROM categorylinks INNER JOIN page ON cl_from=page_id INNER JOIN recentchanges on rc_title=page_title WHERE
(cl_to = 'Kvinnor' OR cl_to = 'Män') AND rc_type = 1 AND
page_title IN (
SELECT rc_title FROM recentchanges WHERE
rc_type = 1 AND rc_namespace = 0
)
GROUP BY rc_user_text
HAVING (antal>=20)
ORDER BY antal DESC
*/

/*
SELECT cl_from, cl_to, pp_propname, pp_value FROM categorylinks INNER JOIN page on cl_to=page_title INNER JOIN page_props on page_id = pp_page 
WHERE cl_from IN ('1466','130404','6485','33780','211320','108436','6808704') 
ORDER BY cl_from, cl_to
/*
SELECT CONCAT(LEFT(rc_timestamp,4),'-',MID(rc_timestamp,5,2),'-',MID(rc_timestamp,7,2)) AS datum_kl_00_till_12_UTC, SUM(IF(cl_to='Kvinnor',1,0)) AS Redigeringar_i_artiklar_om_kvinnor, SUM(IF(cl_to='Män',1,0)) AS Redigeringar_i_artiklar_om_män FROM recentchanges INNER JOIN page on rc_cur_id = page_id INNER JOIN categorylinks ON page_id = cl_from
WHERE (rc_type = 0 OR rc_type = 1) AND page_namespace = 0 AND (cl_to = 'Kvinnor' OR cl_to = 'Män') 
AND rc_timestamp > '20180217000000' AND rc_timestamp < '20180318000000'
GROUP BY datum_kl_00_till_12_UTC
ORDER BY datum_kl_00_till_12_UTC
*/

/*
SELECT log_action AS action, log_timestamp AS tidpunkt, CONCAT('[[',log_title,']]') AS artikel, log_user_text AS användare, log_comment AS kommentar FROM logging
*/
/*
SELECT log_user_text, COUNT(*) AS antal, MIN(log_timestamp) AS first, MAX(log_timestamp) AS last FROM logging
WHERE log_action = 'delete'  AND log_timestamp > '20170223110000'
GROUP BY log_user_text
ORDER BY antal DESC
LIMIT 100
*/
/*
SELECT cl_to, COUNT(cl_from), SUM(page_len) from categorylinks INNER JOIN page on cl_from=page_id WHERE
cl_to = 'Kvinnor' OR cl_to = 'Män'
GROUP BY cl_to
*/

/*
SELECT COUNT(cl_from), SUBSTR(rev_timestamp,1,4) AS year FROM categorylinks INNER JOIN page on cl_from=page_id INNER JOIN revision ON page_id=rev_page WHERE
cl_to = 'Kvinnor' AND rev_parent_id=0 
GROUP BY year
*/

/*
SELECT rc_timestamp, rc_user_text, rc_title, rc_cur_id, rc_this_oldid, rc_last_oldid  FROM recentchanges 
WHERE rc_comment LIKE '%Rullade%' AND rc_namespace = 0
ORDER BY rc_timestamp DESC
*/

/*
SELECT * FROM logging WHERE
log_title IN (SELECT log_title FROM logging WHERE
log_timestamp > '20170615000000' AND
              log_type='block' AND log_params LIKE "%2018-06-15%")
ORDER BY log_timestamp DESC
*/

/*
USE wikidatawiki_p;
DESCRIBE page;
SELECT * FROM page WHERE
page_is_new > 0
AND page_touched > '20180127000000'
LIMIT 100
*/

/*

DESCRIBE langlinks;
SELECT * FROM langlinks
WHERE ll_lang = 'ceb'

LIMIT 100;
*/
/*
SELECT cl_from, cl_to FROM categorylinks WHERE
                      (cl_to LIKE '%Harju_län' OR cl_to LIKE '%Hiiu_län' OR cl_to LIKE '%Ida-Viru_län' OR
                       cl_to LIKE '%Jõgeva län' OR cl_to LIKE '%Järva_län' OR cl_to LIKE '%Lääne_län' OR
                       cl_to LIKE '%Lääne-Viru_län' OR cl_to LIKE '%Põlva_län' OR cl_to LIKE '%Pärnu_län' OR
                       cl_to LIKE '%Rapla_län' OR cl_to LIKE '%Saare_län' OR cl_to LIKE '%Tartu_län' OR
                       cl_to LIKE '%Valga_län' OR cl_to LIKE '%Viljandi_län' OR cl_to LIKE '%Võru_län' OR
                       cl_to LIKE '%landskapet_Dagö' OR cl_to LIKE '%landskapet_Ösel') 
*/

/*
DESCRIBE geo_tags;

SELECT rc_timestamp, rc_type, rc_namespace, rc_title, gt_lat, gt_lon FROM recentchanges INNER JOIN page ON rc_cur_id=page_id INNER JOIN geo_tags ON page_id=gt_page_id
WHERE rc_user_text = 'Larske' AND rc_type < 5 AND rc_timestamp > '20171125080000' AND gt_lat > 55 AND gt_lat < 58 and gt_lon < 20
*/

/*
SELECT page_title FROM page WHERE 
page_id IN (SELECT pl_from FROM pagelinks WHERE pl_title='Marockos_administrativa_indelning')
AND
page_id IN (SELECT cl_from FROM categorylinks WHERE cl_to LIKE 'Robotskapade_%artiklar')
*/

/*
SELECT log_timestamp, log_type, log_user_text, log_namespace, log_title, page_title FROM logging INNER JOIN page ON log_page=page_id WHERE
log_timestamp > '20171110000000'
AND log_type != 'patrol'
ORDER BY log_timestamp DESC
*

/*
DESCRIBE geo_tags;
  SELECT concat('[[',replace(page_title,'_',' '),']]') AS Artikel, gt_lat AS Latitud, gt_lon AS Longitud
  FROM page INNER JOIN geo_tags ON page_id=gt_page_id
  WHERE CONCAT(gt_lat, gt_lon) IN 
  (SELECT CONCAT(gt_lat, gt_lon) AS latlon FROM page INNER JOIN geo_tags ON page_id=gt_page_id 
  WHERE gt_page_id IN  (SELECT cl_from FROM categorylinks WHERE cl_to='Robotskapade_Kanadaartiklar' )
  AND gt_primary=1 AND page_namespace=0
  GROUP BY gt_lat, gt_lon
   HAVING COUNT(gt_page_id) > 1 )
  AND gt_primary=1 AND page_namespace=0 
  ORDER BY gt_lat, gt_lon, page_title 
*/  
  /*  
  ORDER BY COUNT(gt_page_id), gt_lat, gt_lon
 */
 /*
 AND page_namespace=0 AND gt_primary=1
 ORDER BY gt_lat, gt_lon, page_title */


/*
SELECT concat('[[',replace(page_title,'_',' '),']]') AS Artikel, gt_lat AS Latitud, gt_lon AS Longitud, (gt_lat-49.5)*(gt_lat-49.5)+(gt_lon+119.5)*(gt_lon+119.5) AS Avstånd FROM page INNER JOIN geo_tags ON page_id=gt_page_id WHERE gt_page_id IN ( SELECT gt_page_id FROM geo_tags 
*/

/*
SELECT CONCAT('[[',replace(page_title,'_',' '),']]') AS Artikel, gt_lat AS Latitud, gt_lon AS Longitud 
FROM page INNER JOIN geo_tags ON page_id=gt_page_id
WHERE gt_lat=60.59277778 AND gt_lon=15.70500000 AND gt_primary=1 AND page_namespace=0
ORDER BY page_title
*/

/*
SELECT CONCAT('[[',replace(page_title,'_',' '),']]') AS Artikel, gt_lat AS Latitud, gt_lon AS Longitud 
FROM page INNER JOIN geo_tags ON page_id=gt_page_id 
WHERE CONCAT(gt_lat, gt_lon) IN (
*/

/*
  SELECT CONCAT(gt_lat, gt_lon) AS latlon, COUNT(gt_page_id) FROM page INNER JOIN geo_tags ON page_id=gt_page_id 
  WHERE gt_page_id IN ( SELECT gt_page_id FROM geo_tags
*/
/*
 WHERE gt_lat>40 AND gt_lat<90 AND gt_lon>-140 AND gt_lon<-50 AND gt_primary=1 AND page_namespace=0 )
*/
/*
 WHERE gt_primary=1 AND page_namespace=0 )
GROUP BY gt_lat, gt_lon
  HAVING COUNT(gt_page_id) > 1 
*/
/*

/*ORDER BY Avstand*/ 
/*
) AND page_namespace=0 AND gt_primary=1
ORDER BY gt_lat, gt_lon, page_title
*/
/* ORDER BY Avstand  
*/
/*
ORDER BY COUNT(gt_page_id) DESC
*/

/*
SELECT page_title FROM page WHERE page_id IN (
SELECT rd_from FROM redirect INNER JOIN page on rd_title=page_title INNER JOIN categorylinks on page_id=cl_from
  WHERE cl_to='Wikipedia:Basartiklar' )
*/

/*
SELECT rc_timestamp, page_title, rc_namespace  FROM recentchanges INNER JOIN page ON rc_title=page_title INNER JOIN categorylinks ON page_id=cl_from WHERE
cl_to='Wikipedia:Basartiklar' AND rc_type=0 AND rc_timestamp>'20171101000000' AND rc_namespace=0
ORDER BY rc_timestamp DESC
*/

/*
SELECT log_timestamp, log_type, log_action, log_params, log_title, ipb_timestamp FROM logging LEFT JOIN ipblocks ON log_title=ipb_address
WHERE log_type='block'
*/

/*
SELECT * FROM ipblocks
ORDER BY ipb_timestamp DESC
LIMIT 100;

SELECT * FROM logging
ORDER BY log_timestamp DESC
LIMIT 100;

SELECT log_title, log_timestamp, ipb_timestamp, ipb_reason, ipb_expiry, ipb_by_text FROM logging INNER JOIN ipblocks ON log_title=ipb_address
WHERE log_timestamp > '20170101000000' 
  AND log_type = 'newusers' 
  AND log_action = 'create' 
  AND log_timestamp < ipb_timestamp
*/      

/*
SELECT * FROM recentchanges
WHERE rc_timestamp > '2017103100000' AND rc_log_type = 'block'
ORDER BY rc_timestamp
*/

/*
SELECT COUNT(*) AS antal FROM templatelinks WHERE
tl_title='För'
*/

/*
SELECT substr(rc_timestamp,1,12) AS minut, COUNT(*) AS antal  FROM recentchanges WHERE
  rc_user_text = 'InternetArchiveBot' AND rc_type<5
  GROUP BY minut
  ORDER BY minut DESC
*/

/*
SELECT DISTINCT CONCAT('[[',replace(page_title,'_',' '),']]') AS Artikel FROM categorylinks cl1 INNER JOIN categorylinks cl2 ON cl1.cl_from=cl2.cl_from INNER JOIN page on page_id=cl1.cl_from WHERE
  cl1.cl_to LIKE "Politikåret%" AND cl2.cl_to LIKE "Politik_i_Sverige%"
ORDER BY page_title
*/

/*
SELECT DISTINCT CONCAT('[[WP:',pl_title,']]') AS Genväg, CONCAT('[[',IF(rd_namespace='4','Wikipedia:',IF(rd_namespace='10','Mall:',IF(rd_namespace='14','Kategori:',IF(rd_namespace='100','Portal:','')))),replace(rd_title,"_"," "),']]') AS Sida FROM pagelinks INNER JOIN page on pl_title=page_title INNER JOIN redirect on page_id=rd_from 
WHERE pl_namespace = '4' AND page_namespace = '4' AND pl_title=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(pl_title,"a","A"),"b","B"),"c","C"),"d","D"),"e","E"),"f","F"),"g","G"),"h","H"),"i","I"),"j","J"),"k","K"),"l","L"),"m","M"),"n","N"),"o","O"),"p","P"),"q","Q"),"r","R"),"s","S"),"t","T"),"u","U"),"v","V"),"w","W"),"x","X"),"y","Y"),"z","Z"),"å","Å"),"ä","Ä"),"ö","Ö")
ORDER BY pl_title  
*/

/*
DESCRIBE ipblocks;

SELECT * FROM ipblocks INNER JOIN user ON user_id=ipb_by
/* WHERE ipb_expiry = '20180614220000' */
/*
ORDER BY ipb_timestamp
*/

/*
SELECT pl_title, pl_namespace, page_namespace FROM pagelinks INNER JOIN page ON pl_from=page_id WHERE 
page_title = 'Lista_över_arkitektoniska_ord_och_begrepp' 
*/

/*
SELECT TIMESTAMPDIFF(MINUTE, '20000101000000', '20000102000000')
*/

/*
SELECT SUBSTR(rc_timestamp,1,8) AS day, SUM(IF(rc_new=1,1,0)) AS new, SUM(IF(rc_new=0,1,0)) AS edits, SUM(if(rc_new<2,1,0)) as tot FROM recentchanges WHERE
rc_user_text = 'Lsjbot'
GROUP BY day
ORDER BY day
*/

/*
SELECT rc_user_text AS IP, rc_comment AS kommentar, COUNT(*) AS 'antal redigeringar', 
MIN(rc_timestamp) AS 'forsta redigeringstid (UTC)', MAX(rc_timestamp) AS 'sista redigeringstid (UTC)',
TIMESTAMPDIFF(SECOND, MIN(rc_timestamp),MAX(rc_timestamp)) AS 'tidspann (sekunder)'
FROM recentchanges  WHERE
rc_timestamp > '20170817000000' AND
rc_comment IN ('ingenting','idioter','ingentiing', 'idoter', 'idpter')
GROUP BY rc_user_text, rc_comment
ORDER BY 'forsta redigeringstid (UTC)'
*/

/*
SELECT REPLACE(page_title,'_',' '), gt_type, gt_primary, gt_lat, gt_lon FROM geo_tags INNER JOIN page ON gt_page_id=page_id INNER JOIN categorylinks ON page_id=cl_from
WHERE
cl_to = 'Tätorter_i_Sverige'
AND gt_primary = '1'
ORDER BY page_title
*/

/*
SELECT substr(log_timestamp,1,8) as dag, COUNT(*) AS antal FROM logging 
WHERE log_type='delete'
GROUP BY dag
ORDER BY dag DESC
LIMIT 100
*/

/*
SELECT pp_propname, COUNT(*) AS antal, AVG(page_len) AS sidstorlek_medel, MIN(page_len) AS sidstorlek_min, 
MAX(page_len) AS sidstorlek_max FROM page_props INNER JOIN page ON pp_page=page_id
WHERE
pp_propname LIKE 'wikibase-badge-Q%' AND pp_value=1
AND page_is_redirect=0
GROUP BY pp_propname
ORDER BY pp_propname
LIMIT 100
*/

/*
DESCRIBE heartbeat_p.heartbeat;
SELECT * FROM heartbeat_p.heartbeat;
DESCRIBE meta_p.wiki;
SELECT * FROM meta_p.wiki;
SELECT lag FROM heartbeat_p.heartbeat JOIN meta_p.wiki ON CONCAT(shard, '.labsdb') = slice WHERE dbname = 'svwiki';
*/

/*
SELECT concat('[[', rc_title, ']]') AS sida, SUM(rc_new_len-rc_old_len) AS tillagt FROM recentchanges 
WHERE rc_user_text='Larske' 
AND rc_namespace=0
GROUP BY sida
ORDER BY tillagt DESC
LIMIT 100
*/

/*
DESCRIBE categorylinks;
SELECT page_title, cl_to, cl_timestamp, cl_sortkey, cl_sortkey_prefix, cl_collation, cl_type FROM categorylinks 
INNER JOIN page on cl_from=page_id
ORDER BY cl_timestamp DESC 
LIMIT 1000
*/

/*
DESCRIBE geo_tags;
*/

/*SELECT CONCAT('[[', page_title, ']]'), CONCAT('{{coord|', gt_lat, '|', gt_lon, '|display=inline}}')*/ 

/*
SELECT page_title, gt_lat, gt_lon
FROM geo_tags INNER JOIN pagelinks ON gt_page_id=pl_from 
INNER JOIN page ON pl_from=page_id 
*/

/*INNER JOIN categorylinks ON pl_from=cl_from*/

/*
INNER JOIN templatelinks ON tl_from=gt_page_id 
*/

/*WHERE pl_title='Tropikal' AND tl_title='Paghimo_ni_bot'*/

/*
WHERE pl_title='Tropiskt_klimat' AND tl_title='Robotskapad' /*  # AND gt_lat<50.0 AND gt_lat>-50 */


/* cl_to = 'Robotskapade_Storbritannienartiklar' */

/*
ORDER BY gt_lat, gt_lon
*/

/*
SELECT pp_propname, page_title FROM page_props INNER JOIN page ON pp_page=page_id 
WHERE pp_propname LIKE 'wikibase-badge%'
ORDER BY pp_propname, page_title
LIMIT 2000
*/
/*
DESCRIBE category;

SELECT page_title FROM  page LEFT OUTER JOIN category ON page_title=cat_title WHERE cat_title IS NULL AND page_namespace=14
LIMIT 100
*/

/*
SELECT * FROM recentchanges INNER JOIN revision ON rc_cur_id=rev_page  INNER JOIN text on old_id=rev_page  WHERE 
rc_user_text='LarskeBot' AND rc_timestamp > '20170702204500' AND rc_timestamp < '20170702205130' AND rc_namespace = 0

LIMIT 1000
*/

/*
DESCRIBE geo_tags;

SELECT page_title, gt_lat, gt_lon, gt_name FROM geo_tags INNER JOIN page ON gt_page_id=page_id WHERE
page_title='Sandlåda_för_Wikidata/Larske'
*

/*SELECT CONCAT('[[',pl_title,']]') AS Titel, CONCAT('{{Nts|',COUNT(*),'}}') AS Antal FROM pagelinks WHERE
pl_title LIKE '%(huvudstaden)' AND pl_namespace=0 AND pl_from_namespace=0
GROUP BY pl_title
ORDER BY pl_title

*/

/*
SELECT rev_user_text, user_editcount, user_registration, MAX(rev_timestamp) AS senaste FROM user INNER JOIN revision on user_id=rev_user
WHERE user_registration > '20160101000000' AND user_registration < '20170101000000'
GROUP BY rev_user_text
ORDER BY user_editcount DESC
*/

/*USE wikidatawiki_p;

SHOW tables;

DESCRIBE recentchanges;

SELECT rc_user_text, SUM(IF(true,1,0)) AS Totalt, SUM(IF(rc_title='Q4115189',1,0)) AS Sandlada, SUM(IF(rc_title='Q13406268',1,0)) AS Sandlada2, SUM(IF(rc_title='Q15397819',1,0)) AS Sandlada3 FROM recentchanges
WHERE rc_title IN ('Q4115189', 'Q13406268', 'Q15397819')
/*
AND rc_timestamp > '20170601000000'
*/
/*
GROUP BY rc_user_text
UNION ALL
SELECT "total", SUM(IF(true,1,0)) AS Totalt, SUM(IF(rc_title='Q4115189',1,0)) AS Sandlada, SUM(IF(rc_title='Q13406268',1,0)) AS Sandlada2, SUM(IF(rc_title='Q15397819',1,0)) AS Sandlada3 FROM recentchanges
WHERE rc_title IN ('Q4115189', 'Q13406268', 'Q15397819')
*/
/*
SELECT cl_to, COUNT(DISTINCT cl_from) AS antal_sidor, COUNT(rev_page) AS antal_revisioner, MIN(rev_timestamp) AS första, MAX(rev_timestamp) AS senaste FROM revision 
INNER JOIN categorylinks ON rev_page=cl_from WHERE
cl_to LIKE 'Robotskapade_%artiklar' AND cl_type='page'
GROUP BY cl_to
ORDER BY första
*/

/*
SET sql_mode='PIPES_AS_CONCAT';
select '- [['||pl_title||']] <!--'|| c ||' Links-->' as output
from (select pl_title , count(*) as c , pl_namespace
      from pagelinks 
      where pl_from_namespace=0 and pl_namespace=0
      group by pl_title, pl_namespace
      having count(*)>100) as pl
  left join page
    on (pl_title=page_title and page_namespace= pl_namespace)
where page_id is null
order by c desc
*/

/*
SELECT pl_namespace, pl_title FROM pagelinks INNER JOIN page ON page_id=pl_from INNER JOIN categorylinks ON page_id=cl_from
WHERE pl_from_namespace = 4 AND page_title='Utmärkta_artiklar' AND cl_to='Utmärkta_artiklar'
*/

/*
SELECT antallangs, COUNT(*) AS antal FROM {
SELECT COUNT(*) AS antallangs FROM langlinks INNER JOIN categorylinks ON ll_from=cl_from INNER JOIN page on cl_from=page_id
WHERE page_namespace = 0 AND cl_to = 'Robotskapade_Islandartiklar'
GROUP by ll_from
}
GROUP BY antallangs
ORDER BY antal 
LIMIT 50
*/


/*
SELECT rc_user_text AS Användare, COUNT(rc_id) AS Redigeringar, MAX(rc_timestamp) AS Senaste  FROM recentchanges
WHERE rc_bot=0 AND rc_type < 5 
GROUP BY rc_user_text
HAVING Redigeringar > 19
ORDER BY Redigeringar DESC;
*/
/*
SELECT user_name, user_editcount from user
ORDER BY user_editcount DESC
LIMIT 100;
*/


/*SELECT el_from, el_to,  substring(el_to,20,999) AS what, INSTR(substring(el_to,21,999),'/') as pos, substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) as del FROM externallinks
*/
/*
SELECT el_from, page_title, substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) AS del FROM externallinks
INNER JOIN page ON el_from=page_id
WHERE el_to LIKE 'http://runeberg.org%' AND /* page_namespace=0 AND 
NOT substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) IN ('authors','adelskal','anrep','biblblad','bioprast','bioprof','biosvman','blaabog','blanchesam','bokobibl','bortgang','brehm','bwgbgteat','danforf','dbl','dialektl','dimh','eckmat','ekbohrn','ensv1948','ensvtekn','eurkonst','famijour','fataburen','fattig25','finminn','gamlagot','gbgpg','haandlex','hagberg/l','hallhist','halvforf','hgsl','histbib','hvar8dag','hvemerhvem','hwsvkvin','ink','jekforf','kalmherd','ksvperson','kxvtid','kulbild','kyrkohist','landostad','lantuppsl','linnstrom','lio1962p','lukat','mbsvlitt','medhist','','narkyrk','nf','nfaa','nfab','nfac','nfad','nfae','nfaf','nfag','nfah','nfai','nfaj','nfak','nfal','nfam','nfan','nfao','nfap','nfaq','nfar','nfas','nfat','nfba','nfbb','nfbc','nfbd','nfbe','nfbf','nfbg','nfbh','nfbi','nfbj','nfbk','nfbl','nfbm','nfbn','nfbo','nfbp','nfbq','nfbr','nfbs','nfbt','nfca','nfcb','nfcc','nfcd','nfce','nfcf','nfcg','nfch','nfci','nfcj','nfck','nfcl','nfcm','nfcn','nfco','nfcp','nfcq','nfcr','nfm','nordflor','norkal','nyaord','oftl','ordochbild','pedagtid','pgnarke','pk','pgosterg','pht','portr427','religrys','rikskal','rosenberg','rostrad','salmonsen','samlaren','sbh','sfl','sjhungryss','skarahal','skarahe1','sonkal','spg','samtid','sqvinnor','statskal','steelswe','stf','strokirk','svcohrs','svda','svea','sveapres','svebiblio','svekon19','svekonst80','sverhist','svetym','svhistfry','svindkal','svkyrhis','svlartid','svlihist','svsf','tektid','thamaros','thamoreb','thamstoc','thamupps','uppfinn','uppsalla','vemardet','vemarhon','vemarvem','vemindu','vemochvad','vemvardet','wiesminn')
GROUP BY del
ORDER BY del DESC
*/

/*
SELECT substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) AS del, COUNT(*) AS antal FROM externallinks
WHERE el_to LIKE 'http://runeberg.org%' AND page_namespace=0 AND
NOT substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) IN ('authors','adelskal','anrep','biblblad','bioprast','bioprof','biosvman','blaabog','blanchesam','bokobibl','bortgang','brehm','bwgbgteat','danforf','dbl','dialektl','dimh','eckmat','ekbohrn','ensv1948','ensvtekn','eurkonst','famijour','fataburen','fattig25','finminn','gamlagot','gbgpg','haandlex','hagberg/l','hallhist','halvforf','hgsl','histbib','hvar8dag','hvemerhvem','hwsvkvin','ink','jekforf','kalmherd','ksvperson','kxvtid','kulbild','kyrkohist','landostad','lantuppsl','linnstrom','lio1962p','lukat','mbsvlitt','medhist','','narkyrk','nf','nfaa','nfab','nfac','nfad','nfae','nfaf','nfag','nfah','nfai','nfaj','nfak','nfal','nfam','nfan','nfao','nfap','nfaq','nfar','nfas','nfat','nfba','nfbb','nfbc','nfbd','nfbe','nfbf','nfbg','nfbh','nfbi','nfbj','nfbk','nfbl','nfbm','nfbn','nfbo','nfbp','nfbq','nfbr','nfbs','nfbt','nfca','nfcb','nfcc','nfcd','nfce','nfcf','nfcg','nfch','nfci','nfcj','nfck','nfcl','nfcm','nfcn','nfco','nfcp','nfcq','nfcr','nfm','nordflor','norkal','nyaord','oftl','ordochbild','pedagtid','pgnarke','pk','pgosterg','pht','portr427','religrys','rikskal','rosenberg','rostrad','salmonsen','samlaren','sbh','sfl','sjhungryss','skarahal','skarahe1','sonkal','spg','samtid','sqvinnor','statskal','steelswe','stf','strokirk','svcohrs','svda','svea','sveapres','svebiblio','svekon19','svekonst80','sverhist','svetym','svhistfry','svindkal','svkyrhis','svlartid','svlihist','svsf','tektid','thamaros','thamoreb','thamstoc','thamupps','uppfinn','uppsalla','vemardet','vemarhon','vemarvem','vemindu','vemochvad','vemvardet','wiesminn')
GROUP BY del
ORDER BY antal DESC
*/

/*
DESCRIBE geo_tags;

SELECT gt_lat, gt_lon, gt_primary, rc_title, rc_timestamp, rc_type FROM recentchanges
INNER JOIN geo_tags ON gt_page_id = rc_cur_id
WHERE rc_user_text='Larske' AND rc_type<5 AND gt_primary=1 AND gt_lat>55 AND gt_lat<69.5 AND gt_lon>10 AND gt_lon<25
ORDER BY rc_timestamp ASC
*/

/*
SELECT COUNT(*) FROM templatelinks;
*/

/*
SELECT NOW(), rc_timestamp FROM recentchanges
ORDER BY rc_timestamp DESC
LIMIT 10
*/

/*
SELECT MID(rc_timestamp,1,10) AS timme, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170728000000' AND rc_timestamp < '20170730060000' AND rc_bot LIKE '0' AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY timme
ORDER BY timme 
LIMIT 1000;


USE trwiki_p;
SELECT MID(rc_timestamp,1,10) AS timme, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170702800000' AND rc_timestamp < '20170730060000' AND rc_bot LIKE '0'  AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY timme
ORDER BY timme 
LIMIT 1000
*/

/*
SELECT COUNT(*)-58 FROM recentchanges 
WHERE rc_user_text = 'LarskeBot' AND rc_timestamp > '20170614110700'
LIMIT 50
*/

/*
SELECT pl_title FROM pagelinks INNER JOIN page on pl_title=page_title
WHERE pl_from = page_id AND pl_from_namespace = pl_namespace
LIMIT 1000
*/

/*
USE svwiki_p;
SELECT MID(rc_timestamp,1,8) AS dygn, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170422000000' AND rc_timestamp < '2017050600000' AND rc_bot LIKE '0' AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY dygn
ORDER BY dygn 
LIMIT 1000;

USE trwiki_p;
SELECT MID(rc_timestamp,1,8) AS dygn, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170422000000' AND rc_timestamp < '20170506000000' AND rc_bot LIKE '0' AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY dygn
ORDER BY dygn 
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...
