Test 3 - Quarry

SQL

# Find ckbwiki articles in the "زیندووان" category with a Wikidata item and a date of death (P570)

use ckbwiki_p;

SELECT
    CONCAT('# [[', p.page_title, ']]') AS 'Page title'
FROM page AS p
LEFT JOIN page_props AS pp ON p.page_id = pp.pp_page AND pp.pp_propname = 'wikibase_item'
WHERE p.page_namespace = 0
    AND p.page_id IN (SELECT cl_from FROM categorylinks WHERE cl_to = 'زیندووان')
    AND p.page_is_redirect = 0
    AND pp.pp_value IS NOT NULL
    AND p.page_id IN (
        SELECT ips_site_page
        FROM wb_items_per_site
        WHERE ips_site_id = 'ckbwiki'
            AND ips_item_id IN (
                SELECT DISTINCT CONCAT('Q', SUBSTRING_INDEX(pp.pp_value, ':', -1))
                FROM page_props AS pp
                WHERE pp.pp_propname = 'wikibase_item'
            )
    )
    AND p.page_id IN (
        SELECT ips_site_page
        FROM wb_terms
        WHERE term_full_entity_id IN (
            SELECT ips_item_id
            FROM wb_items_per_site
            WHERE ips_site_id = 'wikidatawiki'
        )
    )
    AND EXISTS (
        SELECT 1
        FROM wikidatawiki_p.wbclaims AS c
        WHERE c.claim_entity_type = 'item'
            AND c.claim_id = p.page_title
            AND c.claim_property = 'P570' -- P570 corresponds to date of death
    )
ORDER BY p.page_title ASC;
SQL

Checking query status...
