Fork of trwiki:Most frequent unexisting links by Evolutionoftheuniverse
This query is marked as a draft This query has been published by Evolutionoftheuniverse.

SQL

AخA
 
USE trwiki_p;
SELECT pl_title, COUNT(pl_title) AS counting, arc.ar_len, SUM(CAST(r.ar_len AS INT) - COALESCE(CAST(rp.ar_len AS INT),0)) AS mirada_bytes
FROM pagelinks
JOIN page ON pl_from = page_id AND pl_from_namespace = 0 AND pl_namespace = 0
JOIN archive AS arc ON 
ar_title=pl_title 
AND ar_timestamp <= (SELECT TIMESTAMP("2017-03-29 16:26:55")) 
AND ar_id=(SELECT MAX(ar_id) FROM archive AS a1 WHERE a1.ar_title=arc.ar_title AND ar_namespace=0 AND ar_timestamp <= (SELECT TIMESTAMP("2017-03-29 16:26:55")))
AND ar_namespace=0
JOIN archive r ON arc.ar_title=r.ar_title AND r.ar_actor=2732 
AND r.ar_namespace=0 AND r.ar_timestamp <= arc.ar_timestamp
AND r.ar_id IN (SELECT ar_id FROM archive AS a1 WHERE a1.ar_title=arc.ar_title AND ar_namespace=0 AND a1.ar_title=r.ar_title)
LEFT JOIN archive rp ON 
  r.ar_parent_id = rp.ar_id  AND r.ar_namespace=0
  AND rp.ar_id IN (SELECT ar_id FROM archive AS a1 WHERE a1.ar_title=arc.ar_title AND ar_namespace=0 AND a1.ar_title=rp.ar_title)
WHERE 
NOT EXISTS
(SELECT p1.page_title FROM page AS p1
WHERE p1.page_title = pl_title
AND pl_namespace = 0
AND p1.page_namespace = 0)
AND pl_title IN (SELECT pl_title FROM pagelinks 
                 WHERE pl_from = 2219492 AND pl_from_namespace = 4 AND pl_namespace = 0)
GROUP BY pl_title
ORDER BY pl_title ASC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...