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

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 $$
BEGIN
    RETURN QUERY
        SELECT
            EXTRACT(YEAR FROM e.purchase_date)::INTEGER AS year,
            EXTRACT(MONTH FROM e.purchase_date)::INTEGER AS month,
            COUNT(e.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.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 $$
BEGIN
    RETURN QUERY
        SELECT
            EXTRACT(YEAR FROM p.payment_date)::INTEGER AS year,
            EXTRACT(MONTH FROM p.payment_date)::INTEGER AS month,
            c.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.active::BOOLEAN AS is_version_active,
            COUNT(e.id)::BIGINT AS total_paid_enrollments,
            COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
            SUM(p.amount)::NUMERIC AS total_revenue,
            COUNT(r.id)::BIGINT AS total_reviews,
            COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating
        FROM course c
                 JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'en'
                 JOIN course_version cv ON c.id = cv.course_id
                 JOIN enrollment e ON cv.id = e.course_version_id
                 JOIN payment p ON e.id = p.enrollment_id AND p.payment_status = 'COMPLETED'
                 LEFT JOIN review r ON e.id = r.enrollment_id
        GROUP BY
            EXTRACT(YEAR FROM p.payment_date),
            EXTRACT(MONTH FROM p.payment_date),
            c.id, ct.id, cv.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 $$
BEGIN
    RETURN QUERY
        SELECT
            c.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.active::BOOLEAN AS is_version_active,
            COUNT(e.id)::BIGINT AS total_enrollments,
            COUNT(CASE WHEN p.payment_status = 'COMPLETED' THEN e.id END)::BIGINT AS paid_enrollments,
            COUNT(CASE WHEN e.purchase_date IS NULL THEN e.id END)::BIGINT AS trial_enrollments,
            COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.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.id)::BIGINT AS total_reviews,
            ROUND(AVG(CASE WHEN e.completion_date IS NOT NULL
                               THEN EXTRACT(EPOCH FROM e.completion_date - e.activation_date) / 86400
                END), 0)::INTEGER AS avg_days_to_complete,
            ROUND(100.0 * COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.id END)::NUMERIC / NULLIF(COUNT(e.id), 0), 2) AS completion_rate_percentage,
            ROUND(AVG(
                          COALESCE(
                                  (SELECT COUNT(CASE WHEN ucp.completed = true THEN 1 END)::NUMERIC * 100.0 /
                                          NULLIF(COUNT(*), 0)
                                   FROM user_course_progress ucp
                                   WHERE ucp.enrollment_id = e.id), 0
                          )
                  ), 2)::NUMERIC AS avg_lecture_completion_percentage
        FROM course c
                 JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'en'
                 JOIN course_version cv ON c.id = cv.course_id
                 LEFT JOIN enrollment e ON cv.id = e.course_version_id      -- left join to include versions with zero enrollments
                 LEFT JOIN payment p ON e.id = p.enrollment_id              -- left join to include enrollments without payments (because enrollment can be in trial)
                 LEFT JOIN review r ON e.id = r.enrollment_id               -- left join to include enrollments without reviews
        GROUP BY c.id, ct.id, cv.id
        HAVING COUNT(DISTINCT e.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 $$
BEGIN
    RETURN QUERY
        SELECT
            ex.id::INTEGER AS expert_id,
            ex.name::TEXT,
            COUNT(DISTINCT c.id)::BIGINT AS courses_created,
            COUNT(DISTINCT e.id)::BIGINT AS total_enrollments,
            COUNT(DISTINCT CASE WHEN p.payment_status = 'COMPLETED' THEN e.id END)::BIGINT AS paid_enrollments,
            COUNT(DISTINCT CASE WHEN e.purchase_date IS NULL THEN e.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.id)::BIGINT AS total_reviews
        FROM expert ex
                 LEFT JOIN expert_course ec ON ex.id = ec.expert_id         -- experts with no courses should be included
                 LEFT JOIN course c ON ec.course_id = c.id                  -- experts with no courses should be included
                 JOIN course_version cv ON c.id = cv.course_id              -- only courses with versions (which is always the case)
                 LEFT JOIN enrollment e ON cv.id = e.course_version_id      -- left join to include courses with zero enrollments
                 LEFT JOIN payment p ON e.id = p.enrollment_id              -- left join to include enrollments without payments
                 LEFT JOIN review r ON e.id = r.enrollment_id               -- left join to include enrollments without reviews
        GROUP BY ex.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(id),
      total_revenue ← COALESCE(SUM(amount), 0) (
      payment ⋈ enrollment_id = 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.id, ct.id, cv.id;
      year ← YEAR(payment_date),
      month ← MONTH(payment_date),
      course_id ← c.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.active,
      total_paid_enrollments ← COUNT(e.id),
      total_students ← COUNT(DISTINCT e.user_id),
      total_revenue ← SUM(p.amount),
      total_reviews ← COUNT(r.id),
      average_rating ← COALESCE(AVG(r.rating), 0) 
    (
      course ⋈ id = course_id ∧ language = 'en' course_translate 
      ⋈ id = course_id course_version 
      ⋈ id = course_version_id enrollment 
      ⋈ id = enrollment_id ∧ payment_status = 'COMPLETED' payment 
      ⟕ 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.id) > 0 (
      γ c.id, ct.id, cv.id;
        course_id ← c.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.active,
        total_enrollments ← COUNT(e.id),
        paid_enrollments ← COUNT(CASE WHEN payment_status = 'COMPLETED' THEN e.id END),
        trial_enrollments ← COUNT(CASE WHEN purchase_date IS NULL THEN e.id END),
        total_completions ← COUNT(CASE WHEN completion_date IS NOT NULL THEN e.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.id),
        avg_days_to_complete ← ROUND(AVG(CASE WHEN completion_date IS NOT NULL THEN EXTRACT(EPOCH FROM completion_date - activation_date) / 86400 END), 0),
        completion_rate_percentage ← ROUND(100.0 * COUNT(CASE WHEN completion_date IS NOT NULL THEN e.id END) / NULLIF(COUNT(e.id), 0), 2),
        avg_lecture_completion_percentage ← ROUND(AVG(COALESCE((SELECT COUNT(CASE WHEN completed = true THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0) FROM user_course_progress WHERE enrollment_id = e.id), 0)), 2) 
      (
        course ⋈ id = course_id ∧ language = 'en' course_translate
        ⋈ id = course_id course_version
        ⟕ id = course_version_id enrollment
        ⟕ id = enrollment_id payment
        ⟕ id = enrollment_id review
      )
    )
  )
)

-- Expert performance summary
π expert_id, name, courses_created, total_enrollments, paid_enrollments, trial_enrollments, total_revenue, avg_rating, total_reviews (
  τ total_revenue DESC NULLS LAST (
    γ ex.id;
      expert_id ← ex.id,
      name ← ex.name,
      courses_created ← COUNT(DISTINCT c.id),
      total_enrollments ← COUNT(DISTINCT e.id),
      paid_enrollments ← COUNT(DISTINCT CASE WHEN payment_status = 'COMPLETED' THEN e.id END),
      trial_enrollments ← COUNT(DISTINCT CASE WHEN purchase_date IS NULL THEN e.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.id) 
    (
      expert
      ⟕ id = expert_id expert_course
      ⟕ course_id = id course
      ⋈ id = course_id course_version
      ⟕ id = course_version_id enrollment
      ⟕ id = enrollment_id payment
      ⟕ id = enrollment_id review
    )
  )
)

---