Напредни извештаи од базата

1. Промет по курс

Опис: Извештајот го прикажува вкупниот промет генериран од сите плаќања за секоја верзија на секој курс, филтриран по корисник и јазик на превод.

SQL:

SELECT
    c.id AS course_id,
    ct.title_short AS course_title,
    cv.version_number AS course_version,
    cv.active AS is_active,
    SUM(p.amount) AS total_amount
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 e.course_version_id = cv.id
         JOIN payment p ON p.enrollment_id = e.id
WHERE e.user_id = :user_id
  AND ct.language = :language
GROUP BY
    c.id,
    ct.title_short,
    cv.version_number;

Параметри:

• user_id: ИД на корисникот
• language: јазик за превод

Релациона алгебра:

π course_id←c.id, course_title←ct.title_short, course_version←cv.version_number, is_active←cv.active, total_amount←SUM(p.amount) (
  γ c.id, ct.title_short, cv.version_number, cv.active; SUM(p.amount) (
    σ e.user_id = :user_id ∧ ct.language = :language (
      course ⋈ c.id = ct.course_id course_translate 
             ⋈ c.id = cv.course_id course_version 
             ⋈ cv.id = e.course_version_id enrollment 
             ⋈ e.id = p.enrollment_id payment
    )
  )
)

---

2. Промет по месец

Опис: Извештајот го прикажува вкупниот промет групиран по година и месец, сортиран од најнов кон најстар период.

SQL:

SELECT
    EXTRACT(YEAR FROM p.payment_date) AS year,
    EXTRACT(MONTH FROM p.payment_date) AS month,
    SUM(p.amount) AS total_revenue
FROM payment p
GROUP BY
    EXTRACT(YEAR FROM p.payment_date),
    EXTRACT(MONTH FROM p.payment_date)
ORDER BY
    year DESC,
    month DESC;

Релациона алгебра:

τ year DESC, month DESC (
  π year←EXTRACT(YEAR FROM payment_date), month←EXTRACT(MONTH FROM payment_date), total_revenue←SUM(amount) (
    γ EXTRACT(YEAR FROM payment_date), EXTRACT(MONTH FROM payment_date); SUM(amount) (
      payment
    )
  )
)

---

3. Најпопуларни курсеви според оценка

Опис: Извештајот ги прикажува сите курсеви со нивните верзии, вкупен број на запишувања, просечна оценка и вкупен број на рецензии, сортирани по најдобра просечна оценка.

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

Параметри:

• language: јазик за превод

Релациона алгебра:

τ average_rating DESC (
  π course_id←c.id, course_title←ct.title_short, course_version←cv.version_number, is_active←cv.active, 
     total_enrollments←COUNT(DISTINCT e.id), average_rating←SUM(r.rating)/COUNT(r.id), total_reviews←COUNT(r.id) (
    γ c.id, ct.id, cv.id; COUNT(DISTINCT e.id), SUM(r.rating), COUNT(r.id) (
      σ ct.language = :language (
        course ⋈ c.id = ct.course_id course_translate 
               ⋈ c.id = cv.course_id course_version 
               ⋈ cv.id = e.course_version_id enrollment 
               ⋈ e.id = r.enrollment_id review
      )
    )
  )
)

---