List authors by number of posts in specific channel

Question

I need to display a list of authors that have posted in a specific channel, sorted by number of posts in that channel. I've got this:

{exp:query sql="SELECT screen_name, total_entries, member_id FROM exp_members ORDER BY total_entries DESC"}
    <li>
        {screen_name} ({total_entries})
    </li>
{/exp:query}

... but that counts entries across all channels, not a specific one. Can't seem to find any plugins to help with this, so guessing it's going to be a more complex query.

Thanks loads for any help!

Answer 1

After reading up a bit on mySQL, I've found an answer, which seems to work. If anyone sees that this is horribly inefficient feel free to disagree!

{exp:query sql="SELECT exp_members.screen_name, COUNT(exp_channel_titles.entry_id) AS number_of_posts FROM exp_channel_titles
    LEFT JOIN exp_members
    ON exp_channel_titles.author_id=exp_members.member_id
    LEFT JOIN exp_member_data
    GROUP BY screen_name
    ORDER BY number_of_posts DESC
"}
    <li>
        {screen_name}, ({number_of_posts})
    </li>
{/exp:query}