Wordpress: Get list of users ordered by post count.

Question

I am trying to come up with a function that will output a list of usernames in wp_users ordered by their post count. Right now I am able to get all users, and I am able to get post counts for individual users, but I am having a difficult time combining those two sets of data. any help would be greatly appreciated!

I'm thinking I will need something that combines these two queries:

SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_author = $auth

SELECT user_nicename FROM $wpdb->users

Answer 1

select 
  {$wpdb->users}.user_nicename, 
  count({$wpdb->posts}.ID) as post_count
from {$wpdb->posts}
  left join {$wpdb->users} on {$wpdb->posts}.post_author = {$wpdb->users}.ID 
where 
  {$wpdb->posts}post_status = 'publish';

Formatted and broken up for readability.