How to get top rated posts using wp query?

Don’t worry. I have found solution for your issue. you can use below code to display 5 top rated posts based on average rating. i have created below function to get top average rated post using sql queries and wp_query functions.

here is updated function for all custom post type.

function top_rated_post_via_comment_of_CPT($post_per_page = 5){
 global $wpdb;

    $results = $wpdb->get_results("SELECT DISTINCT(wp_comments.comment_post_ID), GROUP_CONCAT(wp_comments.comment_iD separator ', ') comment_ids FROM wp_comments JOIN wp_commentmeta ON wp_commentmeta.comment_id = wp_comments.comment_ID GROUP BY wp_comments.comment_post_ID", ARRAY_A);


    foreach($results as $key => $value) {

        $c_post_id = $value['comment_post_ID'];
        $comment_ids = $value['comment_ids'];
        $res = $wpdb->get_results( "SELECT AVG(`meta_value`) as avg_rate FROM wp_commentmeta WHERE `meta_key` = 'rating' AND comment_ID IN ($comment_ids) ORDER BY meta_value" );
       $results[$key]['avg_rate'] = $res[0]->avg_rate;
    }
    # sort value by high rated
    $avg_rate = array_column($results, 'avg_rate');
    array_multisort($avg_rate, SORT_DESC, $results);

    $top_rated = array();
    foreach ($results as $result) {

        if($result['avg_rate'] && $result['comment_ids'] )
        {
            $top_rated[] = $result['comment_post_ID'];
        }
    }

    $args = array(
        'post_type' => array("post","movies"),
        'posts_per_page' => $post_per_page,
        'post__in' => $top_rated,
        'orderby' => 'post__in' 
    );

    $top_rated_posts = new WP_Query( $args );

    // The Loop
    if ( $top_rated_posts->have_posts() ) {
        echo '<ul>';

        while ( $top_rated_posts->have_posts() ) {
            $top_rated_posts->the_post();
            $new_key = array_search(get_the_id(), array_column($results, 'comment_post_ID'));
            echo '<li>Post Name : ' . get_the_title() . ' | Average Rate :'.number_format((float)$results[$new_key]['avg_rate'], 2, '.', '').'</li>';

        }
        echo '</ul>';

        wp_reset_postdata();
    } else {
        // no posts found
    }
}

echo top_rated_post_via_comment_of_CPT(5);

to add Average column in admin post listing dashboard

 add_filter( 'manage_movies_posts_columns', 'set_custom_edit_columns' );
    add_filter( 'manage_posts_columns', 'set_custom_edit_columns' );
    function set_custom_edit_columns($columns) {
        $columns['avg_rate'] = __( 'Average Rate', 'your_text_domain' );
        return $columns;
    }

// Add the data to the custom columns for the book post type:
add_action( 'manage_posts_custom_column' , 'custom_column', 10, 2 );
add_action( 'manage_movies_custom_column' , 'custom_column', 10, 2 );
function custom_column( $column, $post_id ) {
    switch ( $column ) {

        case 'avg_rate' :
            global  $wpdb;
            $results = $wpdb->get_results("SELECT DISTINCT(wp_comments.comment_post_ID), GROUP_CONCAT(wp_comments.comment_iD separator ', ') comment_ids FROM wp_comments JOIN wp_commentmeta ON wp_commentmeta.comment_id = wp_comments.comment_ID GROUP BY wp_comments.comment_post_ID", ARRAY_A);
            foreach($results as $key => $value) {

                $c_post_id = $value['comment_post_ID'];
                $comment_ids = $value['comment_ids'];
                $res = $wpdb->get_results( "SELECT AVG(`meta_value`) as avg_rate FROM wp_commentmeta WHERE `meta_key` = 'rating' AND comment_ID IN ($comment_ids) ORDER BY meta_value" );
               $results[$key]['avg_rate'] = $res[0]->avg_rate;
            }
            $new_key = array_search($post_id, array_column($results, 'comment_post_ID'));
            if($results[$new_key]['avg_rate']){
            echo number_format((float)$results[$new_key]['avg_rate'], 2, '.', '');
            }
            else
            {
                echo "No rating";
            }
            break;
    }
}

i hope this function can solve you query. let me know if this function helps you!

Thank you!