cswiki analytics

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

/*
SELECT LEFT(rev_timestamp, 8) AS day, COUNT(rev_id),
	SUM(IF(actor_user IS NULL, 1, IF(DATE_ADD(rev_timestamp, INTERVAL -7 DAY) < log_timestamp, 1, 0))) AS is_new
FROM revision
JOIN page ON page_id = rev_page
JOIN actor_revision ON actor_id = rev_actor
LEFT JOIN user ON actor_user IS NOT NULL AND actor_user = user_id
LEFT JOIN logging_logindex
	ON actor_user IS NOT NULL
    AND log_type = 'newusers' AND log_action = 'create' AND log_namespace = 2 AND log_title = REPLACE(actor_name, ' ', '_')
    AND log_timestamp < rev_timestamp
WHERE page_namespace = 0
AND page_is_redirect = 0
AND rev_timestamp > '2020'
GROUP BY LEFT(rev_timestamp, 8);
*/
/*
SELECT DAYOFWEEK(rev_timestamp) AS den, RIGHT(LEFT(rev_timestamp, 10), 2) AS hodina, COUNT(rev_id) AS revertovano
FROM revision
JOIN change_tag ON rev_id = ct_rev_id AND ct_tag_id IN (SELECT ctd_id FROM change_tag_def WHERE ctd_name = 'mw-reverted')
WHERE rev_timestamp BETWEEN '2021' AND '2022'
GROUP BY DAYOFWEEK(rev_timestamp), RIGHT(LEFT(rev_timestamp, 10), 2);
*/
/*
SELECT LEFT(rc_timestamp, 8) AS day,
	DAYOFWEEK(rc_timestamp) AS dayofweek,
    COUNT(rc_id) AS total, COUNT(ct_rc_id) / COUNT(rc_id) AS reverted_ratio,
    SUM(IF(rc_patrolled = 1, 1, 0)) / COUNT(rc_id) AS patrolled_ratio
FROM recentchanges
JOIN actor_recentchanges ON actor_id = rc_actor
LEFT JOIN change_tag ON rc_id = ct_rc_id AND ct_tag_id IN (SELECT ctd_id FROM change_tag_def WHERE ctd_name = 'mw-reverted')
WHERE actor_user IS NULL AND rc_source IN ('mw.edit', 'mw.new')
GROUP BY LEFT(rc_timestamp, 8)
ORDER BY LEFT(rc_timestamp, 8) DESC;
*/

SELECT COUNT(*) AS existing
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 >= '20150428191725';

#SELECT ctd_name, rev_timestamp, log_timestamp AS time_reg, page_is_redirect AS deleted
SELECT COUNT(*) AS existing_translations, MIN(rev_timestamp), MAX(rev_timestamp)
FROM revision
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 = 'contenttranslation';

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

#SELECT ctd_name, ar_timestamp, ar_title, L1.log_timestamp AS time_reg, L2.log_timestamp AS time_del, 1 AS deleted
SELECT COUNT(*) AS deleted_translations, SUM(IF(
  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), 1, 0)) AS deleted_translations_in_bounds
FROM archive
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 L2.log_timestamp > ar_timestamp
WHERE ar_parent_id = 0 AND ar_namespace = 0
AND ctd_name = 'contenttranslation';
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...
