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

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 enrollment e
                 LEFT JOIN payment p
                           ON p.enrollment_id = e.enrollment_id
                               AND p.payment_status = 'completed'   -- само наплатените пари се промет
        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
                 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
        WITH lecture_progress AS (
            SELECT ucp.enrollment_id,
                   COUNT(*) FILTER (WHERE ucp.is_completed) * 100.0 / COUNT(*) AS completed_percentage
            FROM user_course_progress ucp
            GROUP BY ucp.enrollment_id
        )
        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,
            COALESCE(
                    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),
                    0)::NUMERIC AS completion_rate_percentage,
            COALESCE(ROUND(AVG(lp.completed_percentage), 2), 0)::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   -- include versions with zero enrollments
                 LEFT JOIN payment p ON e.enrollment_id = p.enrollment_id               -- include enrollments without payments
                 LEFT JOIN review r ON e.enrollment_id = r.enrollment_id                -- include enrollments without reviews
                 LEFT JOIN lecture_progress lp ON lp.enrollment_id = e.enrollment_id    -- include enrollments with no progress rows
        GROUP BY c.course_id, ct.course_translate_id, cv.course_version_id
        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) (
      enrollment ⟕ enrollment_id = enrollment_id ∧ payment_status = 'completed' payment
    )
  )
)

-- 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
lecture_progress ←
  γ enrollment_id;
    enrollment_id ← enrollment_id,
    completed_percentage ← COUNT(is_completed = true) * 100.0 / COUNT(*) (
    user_course_progress
  )

π 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 (
    γ 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 ← COALESCE(ROUND(100.0 * COUNT(CASE WHEN completion_date IS NOT NULL THEN e.enrollment_id END) / NULLIF(COUNT(e.enrollment_id), 0), 2), 0),
      avg_lecture_completion_percentage ← COALESCE(ROUND(AVG(lp.completed_percentage), 2), 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
      ⟕ enrollment_id = enrollment_id review
      ⟕ enrollment_id = enrollment_id lecture_progress
    )
  )
)

-- 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
    )
  )
)

---