Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

08/07/26 16:15:55 (12 hours ago)

Author:

231175

Comment:

--

AdvancedReports

@@ -23 +23 @@
             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
+        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),
@@ -112 +114 @@
 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,
@@ -131 +139 @@
                                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
+            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   -- 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
+                 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
-        HAVING COUNT(DISTINCT e.enrollment_id) > 0
         ORDER BY total_revenue DESC, completion_rate_percentage DESC;
 END;
@@ -205 +209 @@
       total_enrollments ← COUNT(enrollment_id),
       total_revenue ← COALESCE(SUM(amount), 0) (
-      payment ⋈ enrollment_id = enrollment_id enrollment
+      enrollment ⟕ enrollment_id = enrollment_id ∧ payment_status = 'completed' payment
     )
   )
@@ -240 +244 @@
 
 -- 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 (
-    σ 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
-      )
+    γ 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
     )
   )