Други Развојни Активности

Анализа на перформанси

Извештај за најпопуларни курсеви според оценка

SQL:

SELECT 
    c.id AS course_id,
    ct.title_short AS course_title,
    cv.version_number AS course_version,
    cv.active AS is_active,
    COUNT(DISTINCT e.id) AS total_enrollments,
    SUM(r.rating) / COUNT(r.id) AS average_rating,
    COUNT(r.id) AS total_reviews
FROM course c
JOIN course_translate ct ON c.id = ct.course_id
JOIN course_version cv ON c.id = cv.course_id
JOIN enrollment e ON cv.id = e.course_version_id
JOIN review r ON e.id = r.enrollment_id
WHERE ct.language = :language
GROUP BY c.id, ct.id, cv.id
ORDER BY SUM(r.rating) / COUNT(r.id) DESC

Индекси:

• Сите примарни клучеви (по default): course_id, course_translate_id, course_version_id, enrollment_id, review_id
• Во Course Translate: course_id, {course_id, language}

  CREATE UNIQUE INDEX uk_course_translate_course_language ON course_translate(course_id, language)