Requests for chinese community

gistfile1.sql

/*# Top 5 members with most revision edits (second and beyond revisions) in 2011 (with each person's number of revision edits, first login time and last login time )
## unfortunately we can only count all revisions. Here are the approved ones*/

Select `auth_user`.`username` as 'username', `auth_user`.`date_joined` as 'join date',`auth_user`.`last_login` as 'last login', COUNT(*) as 'revisions' 
FROM `wiki_revision`
JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
JOIN `auth_user` ON `wiki_revision`.`creator_id`=`auth_user`.`id`
WHERE
year(`wiki_revision`.`created`) LIKE '2011'
AND `wiki_revision`.`is_approved` ='1'
AND `wiki_document`.`locale` LIKE 'zh-CN'
GROUP BY `auth_user`.`username` ORDER BY revisions DESC;


/*# Top 5 members with most reviews in 2011 (with each person's  # of reviews, first login time and last login time )*/

Select `auth_user`.`username` as 'username', `auth_user`.`date_joined` as 'join date',`auth_user`.`last_login` as 'last login', COUNT(*) as 'reviews' 
FROM `wiki_revision`
JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
JOIN `auth_user` ON `wiki_revision`.`reviewer_id`=`auth_user`.`id`
WHERE
year(`wiki_revision`.`created`) LIKE '2011'
AND `wiki_document`.`locale` LIKE 'zh-CN'
GROUP BY `auth_user`.`username` ORDER BY reviews DESC;


/*# Top 5 members with most revision edits (second and beyond revisions) in the period of 9.19 - 10.19 (with each person's number of revision edits, first login time and last login time )
## unfortunately we can only count all revisions. Here are the approved ones*/

Select `auth_user`.`username` as 'username', `auth_user`.`date_joined` as 'join date',`auth_user`.`last_login` as 'last login', COUNT(*) as 'revisions' 
FROM `wiki_revision`
JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
JOIN `auth_user` ON `wiki_revision`.`creator_id`=`auth_user`.`id`
WHERE
`wiki_revision`.`created` BETWEEN '2012-09-19 0' AND '2012-10-20 0'
AND `wiki_document`.`locale` LIKE 'zh-CN'
AND `wiki_revision`.`is_approved` ='1'
GROUP BY `auth_user`.`username` ORDER BY revisions DESC;


/*# Top 5 members with most reviews in the period of 9.19 - 10.19 (with each person's number of revision edits, first login time and last login time )*/

Select `auth_user`.`username` as 'username', `auth_user`.`date_joined` as 'join date',`auth_user`.`last_login` as 'last login', COUNT(*) as 'reviews' 
FROM `wiki_revision`
JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
JOIN `auth_user` ON `wiki_revision`.`reviewer_id`=`auth_user`.`id`
WHERE
`wiki_revision`.`created` BETWEEN '2012-09-19 0' AND '2012-10-20 0'
AND `wiki_document`.`locale` LIKE 'zh-CN'
GROUP BY `auth_user`.`username` ORDER BY reviews DESC;


/*# By Translation Quality
#Top 5 members whose articles have been voted most in 2011 (as helpful)*/

Select `auth_user`.`username` as 'username', `auth_user`.`date_joined` as 'join date',`auth_user`.`last_login` as 'last login', COUNT(*) as 'votes' 
FROM `wiki_revision`
JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
JOIN `auth_user` ON `wiki_revision`.`reviewer_id`=`auth_user`.`id`
JOIN `wiki_helpfulvote` ON `wiki_revision`.`id`=`wiki_helpfulvote`.`revision_id`
WHERE
year(`wiki_revision`.`created`) LIKE '2011'
AND `wiki_document`.`locale` LIKE 'zh-CN'
AND `wiki_helpfulvote`.`helpful`='1'
GROUP BY `auth_user`.`username` ORDER BY votes DESC;