Here’s an easy way to show the top commenters on your blog, optionally excluding a user_id (the site owner, eg.):
{exp:query sql="SELECT COUNT(entry_id) AS count, name, email, url, author_id,
weblog_id, comment_date FROM `exp_comments`
WHERE status='o' AND author_id <> '1'GROUP BY email ORDER BY count DESC LIMIT 10"}
{count} comments by {name}<br />
{/exp:query}
If you only want to show the current month, it gets slightly more difficult:
{exp:query sql="SELECT COUNT(entry_id) AS count, name, email, url, author_id,
weblog_id, comment_date FROM `exp_comments`
WHERE FROM_UNIXTIME(comment_date,'%m') = DATE_FORMAT(CURRENT_DATE(), '%m')
AND FROM_UNIXTIME(comment_date,'%y') = DATE_FORMAT(CURRENT_DATE(), '%y')
AND status='o' AND author_id <> '1'GROUP BY email ORDER BY count DESC LIMIT 10"}
{count} comments by {name}<br />
{/exp:query}
Category:Tricks Category:Queries