Changes between Version 1 and Version 2 of advancedreports


Ignore:
Timestamp:
02/04/26 13:12:01 (2 days ago)
Author:
231175
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • advancedreports

    v1 v2  
    11= Напредни извештаи од базата =
    22
    3 == 1. Промет по курс ==
    4 
    5 '''Опис:''' Извештајот го прикажува вкупниот промет генериран од сите плаќања за секоја верзија на секој курс, филтриран по корисник и јазик на превод.
     3== 1. Целосен Admin Dashboard ==
     4
     5'''Опис:''' Извештајот прикажува промет и запишувања по месец, детални информации групирани по курс (верзија на курс) за месец, севкупни детални информации за сите курсеви со нивните верзии во текот на целата нивна историја, детални информации за експерти и нивни перформанси
    66
    77'''SQL:'''
    88{{{
    9 SELECT
    10     c.id AS course_id,
    11     ct.title_short AS course_title,
    12     cv.version_number AS course_version,
    13     cv.active AS is_active,
    14     SUM(p.amount) AS total_amount
    15 FROM course c
    16          JOIN course_translate ct ON c.id = ct.course_id
    17          JOIN course_version cv ON c.id = cv.course_id
    18          JOIN enrollment e ON e.course_version_id = cv.id
    19          JOIN payment p ON p.enrollment_id = e.id
    20 WHERE e.user_id = :user_id
    21   AND ct.language = :language
    22 GROUP BY
    23     c.id,
    24     ct.title_short,
    25     cv.version_number;
     9-- Monthly total enrollments and revenue
     10CREATE OR REPLACE FUNCTION dashboard_monthly_totals()
     11    RETURNS TABLE (
     12                      year INTEGER,
     13                      month INTEGER,
     14                      total_enrollments BIGINT,
     15                      total_revenue NUMERIC
     16                  ) AS $$
     17BEGIN
     18    RETURN QUERY
     19        SELECT
     20            EXTRACT(YEAR FROM e.purchase_date)::INTEGER AS year,
     21            EXTRACT(MONTH FROM e.purchase_date)::INTEGER AS month,
     22            COUNT(e.id)::BIGINT AS total_enrollments,
     23            COALESCE(SUM(p.amount), 0)::NUMERIC AS total_revenue
     24        FROM payment p
     25                 JOIN enrollment e ON p.enrollment_id = e.id
     26        GROUP BY
     27            EXTRACT(YEAR FROM e.purchase_date),
     28            EXTRACT(MONTH FROM e.purchase_date)
     29        ORDER BY year DESC, month DESC;
     30END;
     31$$ LANGUAGE plpgsql;
     32
     33-- Monthly course-specific
     34CREATE OR REPLACE FUNCTION dashboard_monthly_courses()
     35    RETURNS TABLE (
     36                      year INTEGER,
     37                      month INTEGER,
     38                      course_id INTEGER,
     39                      course_name TEXT,
     40                      course_description TEXT,
     41                      course_difficulty TEXT,
     42                      course_price NUMERIC,
     43                      version_number INTEGER,
     44                      is_version_active BOOLEAN,
     45                      total_paid_enrollments BIGINT,
     46                      total_students BIGINT,
     47                      total_revenue NUMERIC,
     48                      total_reviews BIGINT,
     49                      average_rating NUMERIC
     50                  ) AS $$
     51BEGIN
     52    RETURN QUERY
     53        SELECT
     54            EXTRACT(YEAR FROM p.payment_date)::INTEGER AS year,
     55            EXTRACT(MONTH FROM p.payment_date)::INTEGER AS month,
     56            c.id::INTEGER AS course_id,
     57            ct.title_short::TEXT AS course_name,
     58            ct.description_short::TEXT AS course_description,
     59            c.difficulty::TEXT AS course_difficulty,
     60            c.price::NUMERIC AS course_price,
     61            cv.version_number::INTEGER AS version_number,
     62            cv.active::BOOLEAN AS is_version_active,
     63            COUNT(e.id)::BIGINT AS total_paid_enrollments,
     64            COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
     65            SUM(p.amount)::NUMERIC AS total_revenue,
     66            COUNT(r.id)::BIGINT AS total_reviews,
     67            COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating
     68        FROM course c
     69                 JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'en'
     70                 JOIN course_version cv ON c.id = cv.course_id
     71                 JOIN enrollment e ON cv.id = e.course_version_id
     72                 JOIN payment p ON e.id = p.enrollment_id AND p.payment_status = 'COMPLETED'
     73                 LEFT JOIN review r ON e.id = r.enrollment_id
     74        GROUP BY
     75            EXTRACT(YEAR FROM p.payment_date),
     76            EXTRACT(MONTH FROM p.payment_date),
     77            c.id, ct.id, cv.id
     78        ORDER BY year DESC, month DESC, total_revenue DESC, total_students DESC;
     79END;
     80$$ LANGUAGE plpgsql;
     81
     82-- All time course specific, for each course version
     83CREATE OR REPLACE FUNCTION dashboard_course_performance()
     84    RETURNS TABLE (
     85                      course_id INTEGER,
     86                      course_name TEXT,
     87                      course_description TEXT,
     88                      course_difficulty TEXT,
     89                      course_price NUMERIC,
     90                      version_number INTEGER,
     91                      is_version_active BOOLEAN,
     92                      total_enrollments BIGINT,
     93                      paid_enrollments BIGINT,
     94                      trial_enrollments BIGINT,
     95                      total_completions BIGINT,
     96                      total_students BIGINT,
     97                      total_revenue NUMERIC,
     98                      average_rating NUMERIC,
     99                      total_reviews BIGINT,
     100                      avg_days_to_complete INTEGER,
     101                      completion_rate_percentage NUMERIC,
     102                      avg_lecture_completion_percentage NUMERIC
     103                  ) AS $$
     104BEGIN
     105    RETURN QUERY
     106        SELECT
     107            c.id::INTEGER AS course_id,
     108            ct.title_short::TEXT AS course_name,
     109            ct.description_short::TEXT AS course_description,
     110            c.difficulty::TEXT AS course_difficulty,
     111            c.price::NUMERIC AS course_price,
     112            cv.version_number::INTEGER AS version_number,
     113            cv.active::BOOLEAN AS is_version_active,
     114            COUNT(e.id)::BIGINT AS total_enrollments,
     115            COUNT(CASE WHEN p.payment_status = 'COMPLETED' THEN e.id END)::BIGINT AS paid_enrollments,
     116            COUNT(CASE WHEN e.purchase_date IS NULL THEN e.id END)::BIGINT AS trial_enrollments,
     117            COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.id END)::BIGINT AS total_completions,
     118            COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
     119            COALESCE(SUM(CASE WHEN p.payment_status = 'COMPLETED' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
     120            COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating,
     121            COUNT(r.id)::BIGINT AS total_reviews,
     122            ROUND(AVG(CASE WHEN e.completion_date IS NOT NULL
     123                               THEN EXTRACT(EPOCH FROM e.completion_date - e.activation_date) / 86400
     124                END), 0)::INTEGER AS avg_days_to_complete,
     125            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,
     126            ROUND(AVG(
     127                          COALESCE(
     128                                  (SELECT COUNT(CASE WHEN ucp.completed = true THEN 1 END)::NUMERIC * 100.0 /
     129                                          NULLIF(COUNT(*), 0)
     130                                   FROM user_course_progress ucp
     131                                   WHERE ucp.enrollment_id = e.id), 0
     132                          )
     133                  ), 2)::NUMERIC AS avg_lecture_completion_percentage
     134        FROM course c
     135                 JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'en'
     136                 JOIN course_version cv ON c.id = cv.course_id
     137                 LEFT JOIN enrollment e ON cv.id = e.course_version_id      -- left join to include versions with zero enrollments
     138                 LEFT JOIN payment p ON e.id = p.enrollment_id              -- left join to include enrollments without payments (because enrollment can be in trial)
     139                 LEFT JOIN review r ON e.id = r.enrollment_id               -- left join to include enrollments without reviews
     140        GROUP BY c.id, ct.id, cv.id
     141        HAVING COUNT(DISTINCT e.id) > 0
     142        ORDER BY total_revenue DESC, completion_rate_percentage DESC;
     143END;
     144$$ LANGUAGE plpgsql;
     145
     146
     147-- Expert performance summary
     148CREATE OR REPLACE FUNCTION dashboard_expert_performance()
     149    RETURNS TABLE (
     150                      expert_id INTEGER,
     151                      expert_name TEXT,
     152                      courses_created BIGINT,
     153                      total_enrollments BIGINT,
     154                      paid_enrollments BIGINT,
     155                      trial_enrollments BIGINT,
     156                      total_revenue NUMERIC,
     157                      avg_rating NUMERIC,
     158                      total_reviews BIGINT
     159                  ) AS $$
     160BEGIN
     161    RETURN QUERY
     162        SELECT
     163            ex.id::INTEGER AS expert_id,
     164            ex.name::TEXT,
     165            COUNT(DISTINCT c.id)::BIGINT AS courses_created,
     166            COUNT(DISTINCT e.id)::BIGINT AS total_enrollments,
     167            COUNT(DISTINCT CASE WHEN p.payment_status = 'COMPLETED' THEN e.id END)::BIGINT AS paid_enrollments,
     168            COUNT(DISTINCT CASE WHEN e.purchase_date IS NULL THEN e.id END)::BIGINT AS trial_enrollments,
     169            COALESCE(SUM(CASE WHEN p.payment_status = 'COMPLETED' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
     170            COALESCE(AVG(r.rating), 0)::NUMERIC AS avg_rating,
     171            COUNT(r.id)::BIGINT AS total_reviews
     172        FROM expert ex
     173                 LEFT JOIN expert_course ec ON ex.id = ec.expert_id         -- experts with no courses should be included
     174                 LEFT JOIN course c ON ec.course_id = c.id                  -- experts with no courses should be included
     175                 JOIN course_version cv ON c.id = cv.course_id              -- only courses with versions (which is always the case)
     176                 LEFT JOIN enrollment e ON cv.id = e.course_version_id      -- left join to include courses with zero enrollments
     177                 LEFT JOIN payment p ON e.id = p.enrollment_id              -- left join to include enrollments without payments
     178                 LEFT JOIN review r ON e.id = r.enrollment_id               -- left join to include enrollments without reviews
     179        GROUP BY ex.id
     180        ORDER BY total_revenue DESC NULLS LAST;
     181END;
     182$$ LANGUAGE plpgsql;
    26183}}}
    27 
    28 '''Параметри:'''
    29 * user_id: ИД на корисникот
    30 * language: јазик за превод
    31184
    32185'''Релациона алгебра:'''
    33186{{{
    34 π course_id←c.id, course_title←ct.title_short, course_version←cv.version_number, is_active←cv.active, total_amount←SUM(p.amount) (
    35   γ c.id, ct.title_short, cv.version_number, cv.active; SUM(p.amount) (
    36     σ e.user_id = :user_id ∧ ct.language = :language (
    37       course ⋈ c.id = ct.course_id course_translate
    38              ⋈ c.id = cv.course_id course_version
    39              ⋈ cv.id = e.course_version_id enrollment
    40              ⋈ e.id = p.enrollment_id payment
     187-- Monthly total enrollments and revenue
     188π year, month, total_enrollments, total_revenue (
     189  τ year DESC, month DESC (
     190    γ YEAR(purchase_date), MONTH(purchase_date);
     191      year ← YEAR(purchase_date),
     192      month ← MONTH(purchase_date),
     193      total_enrollments ← COUNT(id),
     194      total_revenue ← COALESCE(SUM(amount), 0) (
     195      payment ⋈ enrollment_id = id enrollment
     196    )
     197  )
     198)
     199
     200-- Monthly course-specific
     201π 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 (
     202  τ year DESC, month DESC, total_revenue DESC, total_students DESC (
     203    γ YEAR(payment_date), MONTH(payment_date), c.id, ct.id, cv.id;
     204      year ← YEAR(payment_date),
     205      month ← MONTH(payment_date),
     206      course_id ← c.id,
     207      course_name ← ct.title_short,
     208      course_description ← ct.description_short,
     209      course_difficulty ← c.difficulty,
     210      course_price ← c.price,
     211      version_number ← cv.version_number,
     212      is_version_active ← cv.active,
     213      total_paid_enrollments ← COUNT(e.id),
     214      total_students ← COUNT(DISTINCT e.user_id),
     215      total_revenue ← SUM(p.amount),
     216      total_reviews ← COUNT(r.id),
     217      average_rating ← COALESCE(AVG(r.rating), 0)
     218    (
     219      course ⋈ id = course_id ∧ language = 'en' course_translate
     220      ⋈ id = course_id course_version
     221      ⋈ id = course_version_id enrollment
     222      ⋈ id = enrollment_id ∧ payment_status = 'COMPLETED' payment
     223      ⟕ id = enrollment_id review
     224    )
     225  )
     226)
     227
     228-- All time course specific, for each course version
     229π 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 (
     230  τ total_revenue DESC, completion_rate_percentage DESC (
     231    σ COUNT(DISTINCT e.id) > 0 (
     232      γ c.id, ct.id, cv.id;
     233        course_id ← c.id,
     234        course_name ← ct.title_short,
     235        course_description ← ct.description_short,
     236        course_difficulty ← c.difficulty,
     237        course_price ← c.price,
     238        version_number ← cv.version_number,
     239        is_version_active ← cv.active,
     240        total_enrollments ← COUNT(e.id),
     241        paid_enrollments ← COUNT(CASE WHEN payment_status = 'COMPLETED' THEN e.id END),
     242        trial_enrollments ← COUNT(CASE WHEN purchase_date IS NULL THEN e.id END),
     243        total_completions ← COUNT(CASE WHEN completion_date IS NOT NULL THEN e.id END),
     244        total_students ← COUNT(DISTINCT e.user_id),
     245        total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'COMPLETED' THEN amount ELSE 0 END), 0),
     246        average_rating ← COALESCE(AVG(r.rating), 0),
     247        total_reviews ← COUNT(r.id),
     248        avg_days_to_complete ← ROUND(AVG(CASE WHEN completion_date IS NOT NULL THEN EXTRACT(EPOCH FROM completion_date - activation_date) / 86400 END), 0),
     249        completion_rate_percentage ← ROUND(100.0 * COUNT(CASE WHEN completion_date IS NOT NULL THEN e.id END) / NULLIF(COUNT(e.id), 0), 2),
     250        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)
     251      (
     252        course ⋈ id = course_id ∧ language = 'en' course_translate
     253        ⋈ id = course_id course_version
     254        ⟕ id = course_version_id enrollment
     255        ⟕ id = enrollment_id payment
     256        ⟕ id = enrollment_id review
     257      )
     258    )
     259  )
     260)
     261
     262-- Expert performance summary
     263π expert_id, name, courses_created, total_enrollments, paid_enrollments, trial_enrollments, total_revenue, avg_rating, total_reviews (
     264  τ total_revenue DESC NULLS LAST (
     265    γ ex.id;
     266      expert_id ← ex.id,
     267      name ← ex.name,
     268      courses_created ← COUNT(DISTINCT c.id),
     269      total_enrollments ← COUNT(DISTINCT e.id),
     270      paid_enrollments ← COUNT(DISTINCT CASE WHEN payment_status = 'COMPLETED' THEN e.id END),
     271      trial_enrollments ← COUNT(DISTINCT CASE WHEN purchase_date IS NULL THEN e.id END),
     272      total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'COMPLETED' THEN amount ELSE 0 END), 0),
     273      avg_rating ← COALESCE(AVG(r.rating), 0),
     274      total_reviews ← COUNT(r.id)
     275    (
     276      expert
     277      ⟕ id = expert_id expert_course
     278      ⟕ course_id = id course
     279      ⋈ id = course_id course_version
     280      ⟕ id = course_version_id enrollment
     281      ⟕ id = enrollment_id payment
     282      ⟕ id = enrollment_id review
    41283    )
    42284  )
    … …  
    45287
    46288----
    47 
    48 == 2. Промет по месец ==
    49 
    50 '''Опис:''' Извештајот го прикажува вкупниот промет групиран по година и месец, сортиран од најнов кон најстар период.
    51 
    52 '''SQL:'''
    53 {{{
    54 SELECT
    55     EXTRACT(YEAR FROM p.payment_date) AS year,
    56     EXTRACT(MONTH FROM p.payment_date) AS month,
    57     SUM(p.amount) AS total_revenue
    58 FROM payment p
    59 GROUP BY
    60     EXTRACT(YEAR FROM p.payment_date),
    61     EXTRACT(MONTH FROM p.payment_date)
    62 ORDER BY
    63     year DESC,
    64     month DESC;
    65 }}}
    66 
    67 '''Релациона алгебра:'''
    68 {{{
    69 τ year DESC, month DESC (
    70   π year←EXTRACT(YEAR FROM payment_date), month←EXTRACT(MONTH FROM payment_date), total_revenue←SUM(amount) (
    71     γ EXTRACT(YEAR FROM payment_date), EXTRACT(MONTH FROM payment_date); SUM(amount) (
    72       payment
    73     )
    74   )
    75 )
    76 }}}
    77 
    78 ----
    79 
    80 == 3. Најпопуларни курсеви според оценка ==
    81 
    82 '''Опис:''' Извештајот ги прикажува сите курсеви со нивните верзии, вкупен број на запишувања, просечна оценка и вкупен број на рецензии, сортирани по најдобра просечна оценка.
    83 
    84 '''SQL:'''
    85 {{{
    86 SELECT
    87     c.id AS course_id,
    88     ct.title_short AS course_title,
    89     cv.version_number AS course_version,
    90     cv.active AS is_active,
    91     COUNT(DISTINCT e.id) AS total_enrollments,
    92     SUM(r.rating) / COUNT(r.id) AS average_rating,
    93     COUNT(r.id) AS total_reviews
    94 FROM course c
    95 JOIN course_translate ct ON c.id = ct.course_id
    96 JOIN course_version cv ON c.id = cv.course_id
    97 JOIN enrollment e ON cv.id = e.course_version_id
    98 JOIN review r ON e.id = r.enrollment_id
    99 WHERE ct.language = :language
    100 GROUP BY c.id, ct.id, cv.id
    101 ORDER BY SUM(r.rating) / COUNT(r.id) DESC
    102 }}}
    103 
    104 '''Параметри:'''
    105 * language: јазик за превод
    106 
    107 '''Релациона алгебра:'''
    108 {{{
    109 τ average_rating DESC (
    110   π course_id←c.id, course_title←ct.title_short, course_version←cv.version_number, is_active←cv.active,
    111      total_enrollments←COUNT(DISTINCT e.id), average_rating←SUM(r.rating)/COUNT(r.id), total_reviews←COUNT(r.id) (
    112     γ c.id, ct.id, cv.id; COUNT(DISTINCT e.id), SUM(r.rating), COUNT(r.id) (
    113       σ ct.language = :language (
    114         course ⋈ c.id = ct.course_id course_translate
    115                ⋈ c.id = cv.course_id course_version
    116                ⋈ cv.id = e.course_version_id enrollment
    117                ⋈ e.id = r.enrollment_id review
    118       )
    119     )
    120   )
    121 )
    122 }}}
    123 
    124 ----