I have an E-learning system and I need to create a report to return how many students were approved, disapproved, students who have not accessed for more than 30 days, students who are taking the course and those who have not yet attended.

The tables that were used are

'company'            Tabela de empresas;
'company_place'      Tabela de centro de treinamentos;
'user'               Tabela de usuários;
'user_course'        Tabela que armazena a inscrição de usuários nos cursos;
'user_course_lesson' Registra as aulas assistidas pelo usuário.

The rules are as follows

For approved students : must contain end date and field approved as 1;

For failed students : should contain end date and field approved as 0;

For drop-down students : With no end date, the field approved is 0, and the last update in the user_course_lesson table should be more than 30 days.

For students who have not yet logged in : There should be no attended class in the user_course_lesson table

For students with the course in progress : total of records without a completion date and with the field approved as 0, less students dropping out and students who did not.

The SQL that I'm using is as follows

SELECT
    /* Nome da empresa */
    c.'name',

    /* Alunos aprovados */
    SUM( case when uc.'approved' = "1" AND uc.'finished_at' IS NOT NULL then 1 else 0 end ) as approved,

    /* Alunos reprovados */
    SUM( case when uc.'approved' = "0" AND uc.'finished_at' IS NOT NULL then 1 else 0 end ) as reproved,

    /* Alunos desistentes */
    SUM( case when uc.'approved' = "0" AND uc.'finished_at' IS NULL AND (
        SELECT 
            MAX( case when 'updated_at' IS NULL then 'created_at' else 'updated_at' end ) as "last_access"
        FROM 'user_course_lesson' as cl
        WHERE cl.'user_course_id' = uc.'id'
        ORDER BY 'last_access' DESC
        LIMIT 1
    ) <= DATE_ADD(now(), INTERVAL(-30) DAY) then 1 else 0 end ) as dropout,

    /* Alunos que não acessaram ainda */
    SUM( case when uc.'approved' = "0" AND uc.'finished_at' IS NULL AND (
        SELECT 
            COUNT( cl.'id' ) as "access"
        FROM 'user_course_lesson' as cl
        WHERE cl.'user_course_id' = uc.'id'
    ) = 0 then 1 else 0 end ) as not_access,

    /* Alunos com o curso em andamento */
    SUM( case when uc.'approved' = "0" AND uc.'finished_at' IS NULL then 1 else 0 end ) - 
    SUM( case when uc.'approved' = "0" AND uc.'finished_at' IS NULL AND (
        SELECT 
            MAX( case when 'updated_at' IS NULL then 'created_at' else 'updated_at' end ) as "last_access"
        FROM 'user_course_lesson' as cl
        WHERE cl.'user_course_id' = uc.'id'
        ORDER BY 'last_access' DESC
        LIMIT 1
    ) <= DATE_ADD(now(), INTERVAL(-30) DAY) then 1 else 0 end ) -
    SUM( case when uc.'approved' = "0" AND uc.'finished_at' IS NULL AND (
        SELECT 
            COUNT( cl.'id' ) as "access"
        FROM 'user_course_lesson' as cl
        WHERE cl.'user_course_id' = uc.'id'
    ) = 0 then 1 else 0 end ) as pending,
    count( uc.'id' ) as 'total'
FROM 'company' as c

INNER JOIN 'company_place' as p
    ON p.'company_id' = c.'id'

INNER JOIN 'user' as u
    ON u.'place_id' = p.'id'

INNER JOIN 'user_course' as uc
    ON uc.'user_id' = u.'id';

The problem

SQL repeats many queries, and the time to calculate with few records is already high, around 0.056 seconds, with increasing records that time will increase exponentially.

Ideally, queries in the user_course_lesson table would not repeat, or have another way to write this SQL with JOINs or another form of relationship.