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 SEPARATOR ', ') AS lang,
	GROUP_CONCAT(DISTINCT wbx_text SEPARATOR ', ') 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_text_in_lang_id
        FROM wbt_item_terms, wbt_term_in_lang
        WHERE wbit_term_in_lang_id=wbtl_id AND wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='label')) t1,
        (SELECT wbit_item_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=(SELECT wby_id FROM wbt_type WHERE wby_name='alias')) t2
    WHERE
        t1.wbit_item_id=t2.wbit_item_id AND
     	t1.wbtl_text_in_lang_id=t2.wbtl_text_in_lang_id
    LIMIT 100000) 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...