Wikidata items linking to the same Qid edited by a specified contributor

Fork of User edits count on Wikidata items linking to the same Qid by Poslovitch

This query is marked as a draft This query has been published by Poslovitch.

Wikidata items connected to the same Qid (through any property) that were edited by a specific contributor.

In this query, the items are linked to Université de Lorraine (Q4173330), and the contributor is Heleich54.

SQL

SELECT DISTINCT page.page_title, wbx_text
FROM revision
JOIN page ON rev_page=page_id
JOIN actor ON rev_actor=actor_id
JOIN (
  SELECT pl_from
  FROM pagelinks
  JOIN linktarget ON lt_id=pl_target_id
  WHERE pl_from_namespace = 0
  AND lt_namespace = 0
  AND lt_title = 'Q4173330' # Université de Lorraine
) univ_lorraine
JOIN wbt_item_terms   ON wbit_item_id = SUBSTRING(page.page_title, 1)
JOIN wbt_term_in_lang ON wbit_term_in_lang_id = wbtl_id
JOIN wbt_text_in_lang ON wbtl_text_in_lang_id = wbxl_id
JOIN wbt_text         ON wbxl_text_id = wbx_id
WHERE univ_lorraine.pl_from = page_id
AND wby_id = 1 # label
AND wbxl_language = 'fr'
AND page.page_namespace = 0
AND actor.actor_name = 'Heleich54'
ORDER BY revision.rev_timestamp DESC;

SQL

Checking query status...