empty query - Quarry

This query is marked as a draft This query has been published by Matěj Suchánek.
SQL

/*
SELECT wbc_entity_usage.*, page_namespace, page_title, page_touched FROM wbc_entity_usage
LEFT JOIN page ON page_id = eu_page_id
WHERE eu_aspect = 'X';
/*
SELECT ctd_name, afa_filter, afa_parameters
FROM change_tag_def, abuse_filter_action
WHERE ctd_user_defined = 1
AND afa_consequence = 'tag' AND (
  afa_parameters = ctd_name
  OR afa_parameters LIKE CONCAT('%\n', ctd_name)
  OR afa_parameters LIKE CONCAT(ctd_name, '\n%')
  OR afa_parameters LIKE CONCAT('%\n', ctd_name, '\n%')
);
#*/
/*
SELECT page_title, pl_title FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_namespace = 0 AND pl_title LIKE '%\_(rozcestník)'
AND NOT EXISTS (SELECT 1 FROM page WHERE page_title = pl_title AND page_namespace = pl_namespace)
AND pl_from_namespace = 0;
/*
SELECT page_title, rev_id, rev_parent_id#, rev_timestamp, comment_text
FROM revision_userindex
JOIN comment_revision ON comment_id = rev_comment_id
JOIN page ON page_id = rev_page
JOIN actor_revision ON actor_id = rev_actor
JOIN pagelinks ON pl_from = page_id
WHERE actor_name = 'MatSuBot'
AND page_namespace = 0 AND pl_from_namespace = 0 AND pl_namespace = 120 AND pl_title = 'P166'
AND comment_text LIKE '%; cleanup'
AND rev_timestamp > '201902';
*//*
SELECT sub.title, sub.page_exists,
	(SELECT 1 FROM page WHERE page_namespace = 0 AND page_title = SUBSTRING_INDEX(sub.title, '_(', 1)) AS disambig_exists,
	(SELECT 1 FROM pagelinks JOIN page ON page_id = pl_from
     WHERE page_namespace = 0 AND page_title = SUBSTRING_INDEX(sub.title, '_(', 1)
     AND pl_namespace = 0 AND pl_title = sub.title) AS linked_from_disambig
FROM (
  SELECT page_title AS title, 1 AS page_exists FROM page WHERE page_namespace = 0 AND page_title LIKE 'Dálnice\_A%\_(%)'
  UNION
  SELECT pl_title AS title, 0 AS page_exists FROM pagelinks
  WHERE pl_from_namespace = 0 AND pl_namespace = 0 AND pl_title LIKE 'Dálnice\_A%\_(%)'
  AND NOT EXISTS (SELECT 1 FROM page WHERE page_namespace = 0 AND page_title = pl_title)
) AS sub;
*/
/*
SELECT page_title, rev_timestamp FROM templatelinks
JOIN page ON page_id = tl_from
JOIN actor_revision ON actor_name = page_title
JOIN revision AS talk_rev ON rev_page = page_id
WHERE tl_namespace = 10
AND tl_title = 'Sdílená_IP_škola'
AND tl_from_namespace = 3
AND page_namespace = 3
AND rev_parent_id = 0
AND NOT EXISTS (
  SELECT 1 FROM revision_userindex AS edit_rev
  WHERE edit_rev.rev_timestamp < talk_rev.rev_timestamp AND edit_rev.rev_actor = actor_id
)
AND NOT EXISTS (
  SELECT 1 FROM archive_userindex AS edit_ar
  WHERE edit_ar.ar_timestamp < talk_rev.rev_timestamp AND edit_ar.ar_actor = actor_id
);
*/
/*
SELECT cl_to, cl_sortkey_prefix, article.page_title
FROM categorylinks
JOIN page AS article ON article.page_id = cl_from
JOIN redirect ON rd_namespace = article.page_namespace AND rd_title = article.page_title
JOIN page AS redir ON redir.page_id = rd_from
WHERE article.page_is_redirect = 0 
AND redir.page_namespace = 0 AND redir.page_title = cl_to
AND cl_sortkey_prefix <> ' '
AND rd_fragment = ''
ORDER BY cl_to;
*/
/*
SELECT rc_timestamp, actor_name, rc_namespace, rc_title, comment_text
FROM recentchanges
JOIN comment_recentchanges ON comment_id = rc_comment_id
JOIN actor_recentchanges ON actor_id = rc_actor
WHERE comment_text LIKE '%/moveClaim%'
AND rc_namespace IN (0, 120, 146)
AND actor_user IS NOT NULL
ORDER BY rc_timestamp DESC;
#SELECT * FROM pagelinks WHERE pl_from_namespace = 0 AND pl_namespace = 10 AND pl_title LIKE 'LocMap\_%';
*/

SELECT COUNT(*) FROM revision
JOIN page ON rev_page = page_id
WHERE rev_parent_id = 0 AND page_namespace = 0
AND page_is_redirect = 0 AND rev_timestamp > '2017';

#SELECT ctd_name, rev_timestamp, log_timestamp AS time_reg, page_is_redirect AS deleted
SELECT COUNT(*)
FROM revision AS R1
JOIN page ON page_id = rev_page
JOIN change_tag ON rev_id = ct_rev_id
JOIN change_tag_def ON ctd_id = ct_tag_id
#LEFT JOIN logging_userindex ON log_actor = rev_actor AND log_type = 'newusers' AND log_action = 'create'
WHERE rev_parent_id = 0
AND page_namespace = 0
AND ctd_name IN ('contenttranslation', 'contenttranslation-v2')
AND rev_timestamp > '2017';
# ------------

SELECT COUNT(*)
FROM archive
JOIN logging_logindex ON log_namespace = ar_namespace AND log_title = ar_title
WHERE ar_parent_id = 0 AND ar_namespace = 0
AND log_type = 'delete' AND log_action = 'delete'
AND CONVERT(log_timestamp, DATETIME) BETWEEN DATE_ADD(CONVERT(ar_timestamp, DATETIME), INTERVAL +7 DAY) AND DATE_ADD(CONVERT(ar_timestamp, DATETIME), INTERVAL +30 DAY)
AND ar_timestamp > '2017';

#SELECT ctd_name, ar_timestamp, ar_title, L1.log_timestamp AS time_reg, L2.log_timestamp AS time_del, 1 AS deleted
SELECT COUNT(*)
FROM archive AS A1
JOIN change_tag ON ar_rev_id = ct_rev_id
JOIN change_tag_def ON ctd_id = ct_tag_id
#LEFT JOIN logging_userindex AS L1 ON L1.log_actor = ar_actor AND L1.log_type = 'newusers' AND L1.log_action = 'create'
JOIN logging_logindex AS L2
	ON L2.log_type = 'delete' AND L2.log_action = 'delete' AND L2.log_namespace = ar_namespace AND L2.log_title = ar_title
    AND CONVERT(L2.log_timestamp, DATETIME) BETWEEN DATE_ADD(CONVERT(ar_timestamp, DATETIME), INTERVAL +7 DAY) AND DATE_ADD(CONVERT(ar_timestamp, DATETIME), INTERVAL +30 DAY)
WHERE ar_parent_id = 0
AND ar_namespace = 0
AND ctd_name IN ('contenttranslation', 'contenttranslation-v2')
AND ar_timestamp > '2017';
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...
