[Support request] List Authors by Total Post Count and display with Content Template Block Element

Found a way to get wordpress authors in order by the number of custom posts published.

Here is the code:

$authors = $wpdb->get_results(
    "SELECT
        $wpdb->users.ID AS author_id,
        $wpdb->users.display_name AS author_name,
        COUNT($wpdb->posts.ID) AS published_songs_and_poems,
        COUNT(CASE WHEN $wpdb->posts.post_type = 'song' THEN $wpdb->posts.ID ELSE NULL END) as published_songs,
        COUNT(CASE WHEN $wpdb->posts.post_type = 'poem' THEN $wpdb->posts.ID ELSE NULL END) as published_poems
    FROM $wpdb->users
    JOIN $wpdb->posts
        ON $wpdb->posts.post_author = $wpdb->users.ID
        AND $wpdb->posts.post_type IN('song', 'poem') AND $wpdb->posts.post_status = 'publish'
    GROUP BY author_id
    ORDER BY published_songs_and_poems DESC"
);

array_walk($authors, function($author) {
    echo $author->author_name." has published ".$author->published_songs." song(s) and ".$author->published_poems." poem(s).<br/>";
});

We’ll adjust this code to include regular post type as well.

Is there a way to use Content Block Element that we already have designed to display the results?
It doesn’t have to be on the actual archive page of CPT. We can create another page to list the authors, if it’s too complicated to do this on the archive page.

Thank you, but I couldn’t get it to work.

So far I can sort by order of published date by setting order to DESC or ASC.
Setting orderby = post_count doesn’t do anything.

I’m bad with php and I couldn’t figure out how to write the snippet to do the following:
Author profiles live in team CPT. Each profile is created by a WordPress user.
These users are also authors or regular blog posts post_type('post')
The goal is to display a list of author profiles (team archive) and sort them in order of number of regular blog posts they have contributed.

This is the snippet I’ve been playing with:

function custom_cpt_order($query){
    if( !is_admin() && ( is_post_type_archive('team') ) && empty( $query->query_vars['suppress_filters'] )){
        $query->query_vars['meta_key'] = '';
	$query->query_vars['orderby'] = 'post_count';
        $query->query_vars['order'] = 'DESC';
    }
    return $query;
}

add_filter( 'pre_get_posts', 'custom_cpt_order' );

Thanks a lot for your help.

Hey guys, been on this problem for weeks now.
Any ideas?

Cheers!