How to simplify several MySQL queries that have the same purpose?

I need to be able to print 5 data that are linked to the same author, but evaluate everything in a general way, that is, I do not want to print how many students are enrolled in a course but the total of all those students who have enrolled in one or several courses of the same author and, like this with all the other data, for test subject I am testing with few records, but these are the data that I want to obtain:

Reseñas 2
Cursos 1
Proyectos 1
Estudiantes 1
Calificación del instructor 4.0

I have saved the data from the tables, which I am using: https://www.db-fiddle.com/f/s2uBnyTLgTXqADtVhkgEUv/0

These are the various queries that I am using and, if it is working correctly:

<?php
    $id_author = 1;
    $status_inscribed = "Completed";
    $study_program_intensive_instructor = "intensive";
    $study_program_proyect_instructor = "proyect";
    
    #Valoración general del instructor/autor
    $stmtVI = $con->prepare("SELECT AVG(ra.rating) AS avg_rating,
                                COUNT(ra.rating) valuations
                            FROM tbl_ratings ra
                            LEFT JOIN tbl_courses c ON ra.id_course = c.id_course
                            LEFT JOIN tbl_author a ON c.id_author = a.id_author
                            WHERE a.id_author=?
                            GROUP BY a.id_author");
    $stmtVI->bind_param("i", $id_author);
    $stmtVI->execute();
    $stmtVI->bind_result($avg_rating_instructor, $instructor_valuations);
    $stmtVI->fetch();
    $stmtVI->close();
    $avg_instructor_print = number_format($avg_rating_instructor,1);

    #Total de alumnos de manera general que tiene el instructor/autor
    $stmtEI = $con->prepare("SELECT COUNT(i.id_course)
                            FROM tbl_inscribed i
                            LEFT JOIN tbl_courses c ON i.id_course = c.id_course
                            LEFT JOIN tbl_author a ON c.id_author = a.id_author
                            WHERE a.id_author=? AND i.status=?
                            GROUP BY a.id_author");
    $stmtEI->bind_param("is", $id_author, $status_inscribed);
    $stmtEI->execute();
    $enrolled_instructor = 0;
    $stmtEI->bind_result($enrolled_instructor);
    $stmtEI->fetch();
    $stmtEI->close();

    #Total de cursos intensivos que tiene instructor/autor
    $stmtCII = $con->prepare("SELECT COUNT(c.id_course)
                            FROM tbl_courses c
                            LEFT JOIN tbl_author a ON c.id_author = a.id_author
                            WHERE c.id_author=? AND c.study_program=?
                            GROUP BY a.id_author");
    $stmtCII->bind_param("is", $id_author,$study_program_intensive_instructor);
    $stmtCII->execute();
    $course_intensive_instructor = 0;
    $stmtCII->bind_result($course_intensive_instructor);
    $stmtCII->fetch();
    $stmtCII->close();

    #Total de cursos proyectos que tiene el instructor/autor
    $stmtCIP = $con->prepare("SELECT COUNT(c.id_course)
                            FROM tbl_courses c
                            LEFT JOIN tbl_author a ON c.id_author = a.id_author
                            WHERE c.id_author=? AND c.study_program=?
                            GROUP BY a.id_author");
    $stmtCIP->bind_param("is", $id_author,$study_program_proyect_instructor);
    $stmtCIP->execute();
    $course_proyect_instructor = 0;
    $stmtCIP->bind_result($course_proyect_instructor);
    $stmtCIP->fetch();
    $stmtCIP->close();
?>

As can be seen in almost the majority the same tables are being repeated and, above all, it is based on bringing information from the same author.$id_author = 1;

I have tried to join the tables, to unify everything as follows:

$id_author = 1;
$status_inscribed = "Completed";

$stmt =  $con->prepare("SELECT AVG(ra.rating) AS avg_rating,
                            COUNT(ra.rating) valuations,
                            COUNT(i.id_course) enrolled
                        FROM tbl_courses c
                        LEFT JOIN tbl_ratings ra ON ra.id_course = c.id_course
                        LEFT JOIN tbl_inscribed i ON i.id_course = c.id_course
                        WHERE c.id_author=? AND i.status=?
                        GROUP BY c.id_course, i.id_course");
$stmt->bind_param("is", $id_author, $status_inscribed);
$stmt->execute();
$stmt->bind_result($avg_rating_instructor, $total_instructor_review, $enrolled_instructor);
$stmt->fetch();
$stmt->close();

But it prints the information wrong for example in the table tbl_inscribedthere are two records one that is in status Completedand the other in status Pendingbut the variable $enrolled_instructorprints me 3, when it should print 1by the condition passed in theWHERE

I have changed the order of the tables, but https://ideone.com/LGExif I have not added different ways GROUP BY a.id_author, i.status, i_id_inscribed, i.id_coursewithout success, I only got there because it prints the data wrong and does not continue with the rest of joining the tables.

Can you explain me how to do all this correctly? Or with the tables that can be joined and the others, if it were the case, the query would be left individually.