Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Atlasowa
.
This query is copied from http://quarry.wmflabs.org/query/3811 draft by Matěj Suchánek. 6 Resultsets: 1) number of rows, number of subscribing pages, number of subscribed entities 2) most recent subscriptions 3) most recent edits in Wikidata which updated pages 4) most recent edits which subscribed entities 5) pages having subscribed most items 6) most subscribed items
Toggle Highlighting
SQL
USE dewiki_p; SELECT COUNT(*), COUNT(DISTINCT eu_page_id), COUNT(DISTINCT eu_entity_id) FROM wbc_entity_usage; SELECT page_title, page_namespace, page_id, eu_entity_id, GROUP_CONCAT(DISTINCT eu_aspect SEPARATOR ', ') AS aspects, eu_touched, rev_timestamp AS last_edited FROM wbc_entity_usage JOIN page ON page_id = eu_page_id LEFT JOIN revision ON rev_id = page_latest GROUP BY eu_touched, eu_page_id, eu_entity_id ORDER BY eu_row_id DESC LIMIT 25; SELECT page_title, page_namespace, GROUP_CONCAT(DISTINCT wbc2.eu_entity_id SEPARATOR ', ') AS entities, rc_title AS item, wbc1.eu_touched, rc_timestamp, rev_timestamp AS last_edited FROM (SELECT * FROM wikidatawiki_p.recentchanges WHERE rc_namespace = 0 AND rc_log_type IS NULL) AS rc JOIN wbc_entity_usage AS wbc1 ON wbc1.eu_entity_id = rc_title AND wbc1.eu_touched >= rc_timestamp JOIN wbc_entity_usage AS wbc2 ON wbc2.eu_page_id = wbc1.eu_page_id JOIN page ON page_id = wbc2.eu_page_id JOIN revision ON rev_id = page_latest AND rev_timestamp < rc_timestamp GROUP BY wbc2.eu_page_id HAVING COUNT(DISTINCT wbc2.eu_entity_id) > 2 ORDER BY MAX(wbc2.eu_row_id) DESC LIMIT 25; SELECT rc_title, GROUP_CONCAT(DISTINCT eu_entity_id SEPARATOR ', ') AS entities, rc_timestamp FROM (SELECT * FROM recentchanges WHERE rc_source = "mw.edit") AS changes JOIN wbc_entity_usage ON eu_page_id = rc_cur_id WHERE eu_touched = rc_timestamp GROUP BY eu_touched, eu_page_id HAVING COUNT(DISTINCT eu_entity_id) > 2 ORDER BY MAX(eu_row_id) DESC LIMIT 25; SELECT page_title, COUNT(DISTINCT eu_entity_id), COUNT(eu_aspect), eu_touched FROM wbc_entity_usage JOIN page ON page_id = eu_page_id GROUP BY eu_page_id HAVING COUNT(DISTINCT eu_entity_id) > 25 ORDER BY COUNT(DISTINCT eu_entity_id) DESC, COUNT(eu_aspect) DESC, page_title; SELECT eu_entity_id, COUNT(DISTINCT eu_page_id) FROM wbc_entity_usage GROUP BY eu_entity_id HAVING COUNT(DISTINCT eu_page_id) > 250 ORDER BY COUNT(DISTINCT eu_page_id) DESC;
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...