cswiki analytics

SQL

USE cswiki_p;
SELECT el_to, cs.page_title, pp_value, term_text
FROM (SELECT * FROM externallinks WHERE el_to LIKE "%drusop.nature.cz%" AND el_to LIKE "%ID=%") AS el
JOIN (SELECT * FROM page WHERE page_namespace = 0 AND page_title NOT LIKE "Seznam%") AS cs ON cs.page_id = el_from
JOIN (SELECT * FROM page_props WHERE pp_propname = "wikibase_item") AS props ON pp_page = cs.page_id
JOIN (SELECT * FROM wikidatawiki_p.page WHERE page_namespace = 0) AS wd ON wd.page_title = pp_value
LEFT JOIN wikidatawiki_p.wb_terms ON term_full_entity_id = wd.page_title AND term_language = 'cs' AND term_type = 'description'
WHERE wd.page_id IN (
  SELECT pl_from FROM wikidatawiki_p.pagelinks WHERE pl_namespace = 0 AND pl_title = "Q811534" AND pl_from_namespace = wd.page_namespace
) AND wd.page_id NOT IN (
  SELECT pl_from FROM wikidatawiki_p.pagelinks WHERE pl_namespace = 120 AND pl_title = "P677" AND pl_from_namespace = wd.page_namespace
)
ORDER BY pp_value;
SQL

Checking query status...
