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

1. Целосен Admin Dashboard

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

SQL:

-- Monthly total enrollments and revenue
CREATE OR REPLACE FUNCTION dashboard_monthly_totals()
    RETURNS TABLE (
                      year INTEGER,
                      month INTEGER,
                      total_enrollments BIGINT,
                      total_revenue NUMERIC
                  ) AS $$
#variable_conflict use_column
BEGIN
    RETURN QUERY
        SELECT
            EXTRACT(YEAR FROM e.purchase_date)::INTEGER AS year,
            EXTRACT(MONTH FROM e.purchase_date)::INTEGER AS month,
            COUNT(e.enrollment_id)::BIGINT AS total_enrollments,
            COALESCE(SUM(p.amount), 0)::NUMERIC AS total_revenue
        FROM payment p
                 JOIN enrollment e ON p.enrollment_id = e.enrollment_id
        GROUP BY
            EXTRACT(YEAR FROM e.purchase_date),
            EXTRACT(MONTH FROM e.purchase_date)
        ORDER BY year DESC, month DESC;
END;
$$ LANGUAGE plpgsql;



-- Monthly course-specific
CREATE OR REPLACE FUNCTION dashboard_monthly_courses()
    RETURNS TABLE (
                      year INTEGER,
                      month INTEGER,
                      course_id INTEGER,
                      course_name TEXT,
                      course_description TEXT,
                      course_difficulty TEXT,
                      course_price NUMERIC,
                      version_number INTEGER,
                      is_version_active BOOLEAN,
                      total_paid_enrollments BIGINT,
                      total_students BIGINT,
                      total_revenue NUMERIC,
                      total_reviews BIGINT,
                      average_rating NUMERIC
                  ) AS $$
#variable_conflict use_column
BEGIN
    RETURN QUERY
        SELECT
            EXTRACT(YEAR FROM p.payment_date)::INTEGER AS year,
            EXTRACT(MONTH FROM p.payment_date)::INTEGER AS month,
            c.course_id::INTEGER AS course_id,
            ct.title_short::TEXT AS course_name,
            ct.description_short::TEXT AS course_description,
            c.difficulty::TEXT AS course_difficulty,
            c.price::NUMERIC AS course_price,
            cv.version_number::INTEGER AS version_number,
            cv.is_active::BOOLEAN AS is_version_active,
            COUNT(e.enrollment_id)::BIGINT AS total_paid_enrollments,
            COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
            SUM(p.amount)::NUMERIC AS total_revenue,
            COUNT(r.review_id)::BIGINT AS total_reviews,
            COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating
        FROM course c
                 JOIN course_translate ct ON c.course_id = ct.course_id
                 JOIN language l ON l.id = ct.language_id AND l.value = 'en'
                 JOIN course_version cv ON c.course_id = cv.course_id
                 JOIN enrollment e ON cv.course_version_id = e.course_version_id
                 JOIN payment p ON e.enrollment_id = p.enrollment_id AND p.payment_status = 'completed'
                 LEFT JOIN review r ON e.enrollment_id = r.enrollment_id
        GROUP BY
            EXTRACT(YEAR FROM p.payment_date),
            EXTRACT(MONTH FROM p.payment_date),
            c.course_id, ct.course_translate_id, cv.course_version_id
        ORDER BY year DESC, month DESC, total_revenue DESC, total_students DESC;
END;
$$ LANGUAGE plpgsql;



-- All time course specific, for each course version
CREATE OR REPLACE FUNCTION dashboard_course_performance()
    RETURNS TABLE (
                      course_id INTEGER,
                      course_name TEXT,
                      course_description TEXT,
                      course_difficulty TEXT,
                      course_price NUMERIC,
                      version_number INTEGER,
                      is_version_active BOOLEAN,
                      total_enrollments BIGINT,
                      paid_enrollments BIGINT,
                      trial_enrollments BIGINT,
                      total_completions BIGINT,
                      total_students BIGINT,
                      total_revenue NUMERIC,
                      average_rating NUMERIC,
                      total_reviews BIGINT,
                      avg_days_to_complete INTEGER,
                      completion_rate_percentage NUMERIC,
                      avg_lecture_completion_percentage NUMERIC
                  ) AS $$
#variable_conflict use_column
BEGIN
    RETURN QUERY
        SELECT
            c.course_id::INTEGER AS course_id,
            ct.title_short::TEXT AS course_name,
            ct.description_short::TEXT AS course_description,
            c.difficulty::TEXT AS course_difficulty,
            c.price::NUMERIC AS course_price,
            cv.version_number::INTEGER AS version_number,
            cv.is_active::BOOLEAN AS is_version_active,
            COUNT(e.enrollment_id)::BIGINT AS total_enrollments,
            COUNT(CASE WHEN p.payment_status = 'completed' THEN e.enrollment_id END)::BIGINT AS paid_enrollments,
            COUNT(CASE WHEN p.payment_id IS NULL THEN e.enrollment_id END)::BIGINT AS trial_enrollments,
            COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.enrollment_id END)::BIGINT AS total_completions,
            COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
            COALESCE(SUM(CASE WHEN p.payment_status = 'completed' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
            COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating,
            COUNT(r.review_id)::BIGINT AS total_reviews,
            ROUND(AVG(CASE WHEN e.completion_date IS NOT NULL
                               THEN (e.completion_date - e.activation_date)
                END), 0)::INTEGER AS avg_days_to_complete,
            ROUND(100.0 * COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.enrollment_id END)::NUMERIC / NULLIF(COUNT(e.enrollment_id), 0), 2) AS completion_rate_percentage,
            ROUND(AVG(
                          COALESCE(
                                  (SELECT COUNT(CASE WHEN ucp.is_completed = true THEN 1 END)::NUMERIC * 100.0 /
                                          NULLIF(COUNT(*), 0)
                                   FROM user_course_progress ucp
                                   WHERE ucp.enrollment_id = e.enrollment_id), 0
                          )
                  ), 2)::NUMERIC AS avg_lecture_completion_percentage
        FROM course c
                 JOIN course_translate ct ON c.course_id = ct.course_id
                 JOIN language l ON l.id = ct.language_id AND l.value = 'en'
                 JOIN course_version cv ON c.course_id = cv.course_id
                 LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id   -- left join to include versions with zero enrollments
                 LEFT JOIN payment p ON e.enrollment_id = p.enrollment_id               -- left join to include enrollments without payments (because enrollment can be in trial)
                 LEFT JOIN review r ON e.enrollment_id = r.enrollment_id                -- left join to include enrollments without reviews
        GROUP BY c.course_id, ct.course_translate_id, cv.course_version_id
        HAVING COUNT(DISTINCT e.enrollment_id) > 0
        ORDER BY total_revenue DESC, completion_rate_percentage DESC;
END;
$$ LANGUAGE plpgsql;



-- Expert performance summary
CREATE OR REPLACE FUNCTION dashboard_expert_performance()
    RETURNS TABLE (
                      expert_id INTEGER,
                      expert_name TEXT,
                      courses_created BIGINT,
                      total_enrollments BIGINT,
                      paid_enrollments BIGINT,
                      trial_enrollments BIGINT,
                      total_revenue NUMERIC,
                      avg_rating NUMERIC,
                      total_reviews BIGINT
                  ) AS $$
#variable_conflict use_column
BEGIN
    RETURN QUERY
        SELECT
            ex.expert_id::INTEGER AS expert_id,
            a.name::TEXT AS expert_name,
            COUNT(DISTINCT c.course_id)::BIGINT AS courses_created,
            COUNT(DISTINCT e.enrollment_id)::BIGINT AS total_enrollments,
            COUNT(DISTINCT CASE WHEN p.payment_status = 'completed' THEN e.enrollment_id END)::BIGINT AS paid_enrollments,
            COUNT(DISTINCT CASE WHEN p.payment_id IS NULL THEN e.enrollment_id END)::BIGINT AS trial_enrollments,
            COALESCE(SUM(CASE WHEN p.payment_status = 'completed' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
            COALESCE(AVG(r.rating), 0)::NUMERIC AS avg_rating,
            COUNT(r.review_id)::BIGINT AS total_reviews
        FROM expert ex
                 JOIN account a ON a.id = ex.account_id                                 -- expert name lives on account
                 LEFT JOIN expert_course ec ON ex.expert_id = ec.expert_id              -- experts with no courses should be included
                 LEFT JOIN course c ON ec.course_id = c.course_id                       -- experts with no courses should be included
                 LEFT JOIN course_version cv ON c.course_id = cv.course_id              -- left join so experts without courses are not dropped
                 LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id   -- left join to include courses with zero enrollments
                 LEFT JOIN payment p ON e.enrollment_id = p.enrollment_id               -- left join to include enrollments without payments
                 LEFT JOIN review r ON e.enrollment_id = r.enrollment_id                -- left join to include enrollments without reviews
        GROUP BY ex.expert_id, a.id
        ORDER BY total_revenue DESC NULLS LAST;
END;
$$ LANGUAGE plpgsql;

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

-- Monthly total enrollments and revenue
π year, month, total_enrollments, total_revenue (
  τ year DESC, month DESC (
    γ YEAR(purchase_date), MONTH(purchase_date); 
      year ← YEAR(purchase_date), 
      month ← MONTH(purchase_date),
      total_enrollments ← COUNT(enrollment_id),
      total_revenue ← COALESCE(SUM(amount), 0) (
      payment ⋈ enrollment_id = enrollment_id enrollment
    )
  )
)

-- Monthly course-specific
π year, month, course_id, course_name, course_description, course_difficulty, course_price, version_number, is_version_active, total_paid_enrollments, total_students, total_revenue, total_reviews, average_rating (
  τ year DESC, month DESC, total_revenue DESC, total_students DESC (
    γ YEAR(payment_date), MONTH(payment_date), c.course_id, ct.course_translate_id, cv.course_version_id;
      year ← YEAR(payment_date),
      month ← MONTH(payment_date),
      course_id ← c.course_id,
      course_name ← ct.title_short,
      course_description ← ct.description_short,
      course_difficulty ← c.difficulty,
      course_price ← c.price,
      version_number ← cv.version_number,
      is_version_active ← cv.is_active,
      total_paid_enrollments ← COUNT(e.enrollment_id),
      total_students ← COUNT(DISTINCT e.user_id),
      total_revenue ← SUM(p.amount),
      total_reviews ← COUNT(r.review_id),
      average_rating ← COALESCE(AVG(r.rating), 0) 
    (
      course ⋈ course_id = course_id course_translate 
      ⋈ language_id = id ∧ value = 'en' language 
      ⋈ course_id = course_id course_version 
      ⋈ course_version_id = course_version_id enrollment 
      ⋈ enrollment_id = enrollment_id ∧ payment_status = 'completed' payment 
      ⟕ enrollment_id = enrollment_id review
    )
  )
)

-- All time course specific, for each course version
π course_id, course_name, course_description, course_difficulty, course_price, version_number, is_version_active, total_enrollments, paid_enrollments, trial_enrollments, total_completions, total_students, total_revenue, average_rating, total_reviews, avg_days_to_complete, completion_rate_percentage, avg_lecture_completion_percentage (
  τ total_revenue DESC, completion_rate_percentage DESC (
    σ COUNT(DISTINCT e.enrollment_id) > 0 (
      γ c.course_id, ct.course_translate_id, cv.course_version_id;
        course_id ← c.course_id,
        course_name ← ct.title_short,
        course_description ← ct.description_short,
        course_difficulty ← c.difficulty,
        course_price ← c.price,
        version_number ← cv.version_number,
        is_version_active ← cv.is_active,
        total_enrollments ← COUNT(e.enrollment_id),
        paid_enrollments ← COUNT(CASE WHEN payment_status = 'completed' THEN e.enrollment_id END),
        trial_enrollments ← COUNT(CASE WHEN payment_id IS NULL THEN e.enrollment_id END),
        total_completions ← COUNT(CASE WHEN completion_date IS NOT NULL THEN e.enrollment_id END),
        total_students ← COUNT(DISTINCT e.user_id),
        total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'completed' THEN amount ELSE 0 END), 0),
        average_rating ← COALESCE(AVG(r.rating), 0),
        total_reviews ← COUNT(r.review_id),
        avg_days_to_complete ← ROUND(AVG(CASE WHEN completion_date IS NOT NULL THEN completion_date - activation_date END), 0),
        completion_rate_percentage ← ROUND(100.0 * COUNT(CASE WHEN completion_date IS NOT NULL THEN e.enrollment_id END) / NULLIF(COUNT(e.enrollment_id), 0), 2),
        avg_lecture_completion_percentage ← ROUND(AVG(COALESCE((SELECT COUNT(CASE WHEN is_completed = true THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0) FROM user_course_progress WHERE enrollment_id = e.enrollment_id), 0)), 2) 
      (
        course ⋈ course_id = course_id course_translate
        ⋈ language_id = id ∧ value = 'en' language
        ⋈ course_id = course_id course_version
        ⟕ course_version_id = course_version_id enrollment
        ⟕ enrollment_id = enrollment_id payment
        ⟕ enrollment_id = enrollment_id review
      )
    )
  )
)

-- Expert performance summary
π expert_id, expert_name, courses_created, total_enrollments, paid_enrollments, trial_enrollments, total_revenue, avg_rating, total_reviews (
  τ total_revenue DESC NULLS LAST (
    γ ex.expert_id, a.id;
      expert_id ← ex.expert_id,
      expert_name ← a.name,
      courses_created ← COUNT(DISTINCT c.course_id),
      total_enrollments ← COUNT(DISTINCT e.enrollment_id),
      paid_enrollments ← COUNT(DISTINCT CASE WHEN payment_status = 'completed' THEN e.enrollment_id END),
      trial_enrollments ← COUNT(DISTINCT CASE WHEN payment_id IS NULL THEN e.enrollment_id END),
      total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'completed' THEN amount ELSE 0 END), 0),
      avg_rating ← COALESCE(AVG(r.rating), 0),
      total_reviews ← COUNT(r.review_id) 
    (
      expert
      ⋈ account_id = id account
      ⟕ expert_id = expert_id expert_course
      ⟕ course_id = course_id course
      ⟕ course_id = course_id course_version
      ⟕ course_version_id = course_version_id enrollment
      ⟕ enrollment_id = enrollment_id payment
      ⟕ enrollment_id = enrollment_id review
    )
  )
)

---