Untitled query #73332

SQL

SELECT 
floor(t.log_timestamp/10000000000) as year,
sum(1) as total,
sum(comment_text LIKE "%A1%") as ei_maaritelmaa,
sum(comment_text LIKE "%A2%") as vaara_kieli,
sum(comment_text LIKE "%A3%") as ei_sisaltoa,
sum(comment_text LIKE "%A4%") as vaara_projekti,
sum(comment_text LIKE "%A5%") as epamerkittava,
sum(comment_text LIKE "%A6%") as hok,
sum(comment_text LIKE "%O2%") as ohjaus,
sum(comment_text LIKE "%Y10%") as vahingoittamiseen,
sum(comment_text LIKE "%Y11%") as mainos,
sum(comment_text LIKE "%Y12%") as tekijanoikeus,
sum(comment_text LIKE "%Y13%") as kaksois,
sum(comment_text LIKE "%Y1]%") as ei_jark_sis,
sum(comment_text LIKE "%Y2]%") as massapoist,
sum(comment_text LIKE "%Y3]%") as vandalismi,
sum(comment_text LIKE "%Y4]%") as poistoaanestys,
sum(comment_text LIKE "%Y5]%") as est_kayt_sivu,
sum(comment_text LIKE "%Y6]%") as tekn_poisto,
sum(comment_text LIKE "%Y7]%") as aloit_pyynto

FROM
(
SELECT
    l1.log_id,
    l1.log_title,
    l1.log_page,
    page_id,
    l1.log_timestamp,
    l1.log_actor,
    cx.ct_id
FROM
	logging as l1
    	LEFT JOIN page ON page_id = l1.log_page AND page_is_redirect IS NOT true
        LEFT JOIN change_tag as cx ON cx.ct_log_id=l1.log_id AND cx.ct_tag_id IN ( 
          SELECT ctd_id FROM change_tag_def WHERE ctd_name IN ("contenttranslation", "contenttranslation-v2") 
        )
        LEFT JOIN change_tag as redirect ON redirect.ct_log_id=l1.log_id AND redirect.ct_tag_id IN ( 
          SELECT ctd_id FROM change_tag_def WHERE ctd_name IN ("mw-new-redirect") 
        )
        ,
    actor_logging,
    user
WHERE
	l1.log_namespace=0
	AND l1.log_action="create"
    AND actor_id=l1.log_actor
    AND user_id=actor_user
    AND DATEDIFF(STR_TO_DATE(l1.log_timestamp, "%Y%m%d%H%i%s"), STR_TO_DATE(user_registration, "%Y%m%d%H%i%s")) < 365
    AND cx.ct_id IS NOT NULL
    AND redirect.ct_id IS NULL
    AND page_id IS NULL
GROUP BY l1.log_id
) as t,
logging as l2,
comment_logging
WHERE
t.log_page = l2.log_page 
AND l2.log_type="delete" 
AND l2.log_action = "delete" 
AND t.log_id<l2.log_id
AND l2.log_comment_id=comment_id
group by year
SQL

Checking query status...
