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.

Checking query status...