Fork of
bnwscontest test data
by Jayantanth
This query is marked as a draft
This query has been published
by Mahir256.
SQL
x
use bnwikisource_p;
SELECT actor.actor_name, COUNT(actor.actor_name)
FROM revision
INNER JOIN actor ON revision.rev_actor = actor.actor_id
INNER JOIN page ON page.page_id = revision.rev_page
WHERE page.page_title
LIKE ('বুদ্ধের_জীবন_ও_বাণী.djvu%')
AND page.page_namespace=104
GROUP BY actor.actor_name;
USE bnwikisource_p;
SELECT page_title,rev_comment,rev_id,rev_page,actor_name,rev_timestamp
FROM revision
JOIN actor ON rev_actor = actor_id
JOIN comment ON rev_comment_id = comment_id
JOIN page ON page_id = rev_page
WHERE
page_namespace=104 AND
page_title RLIKE '^বুদ্ধের_জীবন_ও_বাণী.djvu/' AND
comment_text RLIKE '^/\\\* '
ORDER BY rev_timestamp DESC
LIMIT 5000;
SELECT
actor.actor_name as 'Editor',
ndx.page_title as 'Work',
sum(edp.page_namespace = 104) as 'Page-space: edits',
sum(edp.page_namespace = 102) as 'Index-space: edits',
sum(edp.page_namespace = 0) as 'Main-space: edits',
COUNT(edp.page_id)-sum(edp.page_namespace in (0,104,102)) AS 'Other edits'
FROM
bnwikisource_p.page ndx
INNER JOIN
bnwikisource_p.pagelinks lnk ON ndx.page_id = lnk.pl_from
INNER JOIN
bnwikisource_p.page edp ON edp.page_title = lnk.pl_title
INNER JOIN
bnwikisource_p.revision edt ON edp.page_id = edt.rev_page
INNER JOIN
bnwikisource_p.actor actor ON edt.rev_actor = actor.actor_id
WHERE
ndx.page_title IN ('বুদ্ধের_জীবন_ও_বাণী.djvu')
AND ndx.page_namespace = 102 /* N.B. INDEX namespace! */
AND (edt.rev_timestamp > '20170101000000' /* Revision/edit */
AND edt.rev_timestamp < DATE_FORMAT(NOW(), 'YYmmddHHiiss'))
GROUP BY ndx.page_title,edt.rev_user_text;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.