wikidatawiki Alias equal to label

Query breakdown:
- inner 2 queries (t1 & t2): find recently changed items with labels and aliases and their languages
- middle query (t3): find items that use the same text in the same language as both its label and as an alias
- outer query: lookup the texts themselves and the language codes and group them by item ID

Takes ½-2 hours w/o recentchanges, and ½-1 hours with.

SQL

SELECT 
    CONCAT('Q', wbit_item_id) AS `item ID`,
    GROUP_CONCAT(DISTINCT wbxl_language) AS lang,
	GROUP_CONCAT(DISTINCT wbx_text) AS text
FROM
	(SELECT t1.wbit_item_id AS wbit_item_id, t1.wbtl_text_in_lang_id AS wbtl_text_in_lang_id
    FROM
        (SELECT wbit_item_id, wbtl_type_id, wbtl_text_in_lang_id
        FROM wbt_item_terms, wbt_term_in_lang
        WHERE wbit_term_in_lang_id=wbtl_id AND wbtl_type_id IN (SELECT wby_id FROM wbt_type WHERE wby_name IN ('label','alias'))) t1,
        (SELECT wbit_item_id, wbtl_type_id, wbtl_text_in_lang_id
        FROM wbt_item_terms, wbt_term_in_lang
        WHERE wbit_term_in_lang_id=wbtl_id AND wbtl_type_id IN (SELECT wby_id FROM wbt_type WHERE wby_name IN ('label','alias'))) t2
    WHERE
        t1.wbit_item_id=t2.wbit_item_id AND
        t1.wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='label') AND
        t2.wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='alias') AND
     	t1.wbtl_text_in_lang_id=t2.wbtl_text_in_lang_id
    LIMIT 10000) t3,
    wbt_text_in_lang, wbt_text
WHERE
    wbtl_text_in_lang_id=wbxl_id AND
    wbxl_text_id=wbx_id
GROUP BY wbit_item_id

SQL

Checking query status...