Changes between Version 3 and Version 4 of AdvancedReports


Ignore:
Timestamp:
08/06/26 12:50:39 (34 hours ago)
Author:
231175
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v3 v4  
    1515                      total_revenue NUMERIC
    1616                  ) AS $$
     17#variable_conflict use_column
    1718BEGIN
    1819    RETURN QUERY
    … …  
    2021            EXTRACT(YEAR FROM e.purchase_date)::INTEGER AS year,
    2122            EXTRACT(MONTH FROM e.purchase_date)::INTEGER AS month,
    22             COUNT(e.id)::BIGINT AS total_enrollments,
     23            COUNT(e.enrollment_id)::BIGINT AS total_enrollments,
    2324            COALESCE(SUM(p.amount), 0)::NUMERIC AS total_revenue
    2425        FROM payment p
    25                  JOIN enrollment e ON p.enrollment_id = e.id
     26                 JOIN enrollment e ON p.enrollment_id = e.enrollment_id
    2627        GROUP BY
    2728            EXTRACT(YEAR FROM e.purchase_date),
    … …  
    5152                      average_rating NUMERIC
    5253                  ) AS $$
     54#variable_conflict use_column
    5355BEGIN
    5456    RETURN QUERY
    … …  
    5658            EXTRACT(YEAR FROM p.payment_date)::INTEGER AS year,
    5759            EXTRACT(MONTH FROM p.payment_date)::INTEGER AS month,
    58             c.id::INTEGER AS course_id,
     60            c.course_id::INTEGER AS course_id,
    5961            ct.title_short::TEXT AS course_name,
    6062            ct.description_short::TEXT AS course_description,
    … …  
    6264            c.price::NUMERIC AS course_price,
    6365            cv.version_number::INTEGER AS version_number,
    64             cv.active::BOOLEAN AS is_version_active,
    65             COUNT(e.id)::BIGINT AS total_paid_enrollments,
     66            cv.is_active::BOOLEAN AS is_version_active,
     67            COUNT(e.enrollment_id)::BIGINT AS total_paid_enrollments,
    6668            COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
    6769            SUM(p.amount)::NUMERIC AS total_revenue,
    68             COUNT(r.id)::BIGINT AS total_reviews,
     70            COUNT(r.review_id)::BIGINT AS total_reviews,
    6971            COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating
    7072        FROM course c
    71                  JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'en'
    72                  JOIN course_version cv ON c.id = cv.course_id
    73                  JOIN enrollment e ON cv.id = e.course_version_id
    74                  JOIN payment p ON e.id = p.enrollment_id AND p.payment_status = 'COMPLETED'
    75                  LEFT JOIN review r ON e.id = r.enrollment_id
     73                 JOIN course_translate ct ON c.course_id = ct.course_id
     74                 JOIN language l ON l.id = ct.language_id AND l.value = 'en'
     75                 JOIN course_version cv ON c.course_id = cv.course_id
     76                 JOIN enrollment e ON cv.course_version_id = e.course_version_id
     77                 JOIN payment p ON e.enrollment_id = p.enrollment_id AND p.payment_status = 'completed'
     78                 LEFT JOIN review r ON e.enrollment_id = r.enrollment_id
    7679        GROUP BY
    7780            EXTRACT(YEAR FROM p.payment_date),
    7881            EXTRACT(MONTH FROM p.payment_date),
    79             c.id, ct.id, cv.id
     82            c.course_id, ct.course_translate_id, cv.course_version_id
    8083        ORDER BY year DESC, month DESC, total_revenue DESC, total_students DESC;
    8184END;
    … …  
    106109                      avg_lecture_completion_percentage NUMERIC
    107110                  ) AS $$
     111#variable_conflict use_column
    108112BEGIN
    109113    RETURN QUERY
    110114        SELECT
    111             c.id::INTEGER AS course_id,
     115            c.course_id::INTEGER AS course_id,
    112116            ct.title_short::TEXT AS course_name,
    113117            ct.description_short::TEXT AS course_description,
    … …  
    115119            c.price::NUMERIC AS course_price,
    116120            cv.version_number::INTEGER AS version_number,
    117             cv.active::BOOLEAN AS is_version_active,
    118             COUNT(e.id)::BIGINT AS total_enrollments,
    119             COUNT(CASE WHEN p.payment_status = 'COMPLETED' THEN e.id END)::BIGINT AS paid_enrollments,
    120             COUNT(CASE WHEN e.purchase_date IS NULL THEN e.id END)::BIGINT AS trial_enrollments,
    121             COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.id END)::BIGINT AS total_completions,
     121            cv.is_active::BOOLEAN AS is_version_active,
     122            COUNT(e.enrollment_id)::BIGINT AS total_enrollments,
     123            COUNT(CASE WHEN p.payment_status = 'completed' THEN e.enrollment_id END)::BIGINT AS paid_enrollments,
     124            COUNT(CASE WHEN p.payment_id IS NULL THEN e.enrollment_id END)::BIGINT AS trial_enrollments,
     125            COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.enrollment_id END)::BIGINT AS total_completions,
    122126            COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
    123             COALESCE(SUM(CASE WHEN p.payment_status = 'COMPLETED' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
     127            COALESCE(SUM(CASE WHEN p.payment_status = 'completed' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
    124128            COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating,
    125             COUNT(r.id)::BIGINT AS total_reviews,
     129            COUNT(r.review_id)::BIGINT AS total_reviews,
    126130            ROUND(AVG(CASE WHEN e.completion_date IS NOT NULL
    127                                THEN EXTRACT(EPOCH FROM e.completion_date - e.activation_date) / 86400
     131                               THEN (e.completion_date - e.activation_date)
    128132                END), 0)::INTEGER AS avg_days_to_complete,
    129             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,
     133            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,
    130134            ROUND(AVG(
    131135                          COALESCE(
    132                                   (SELECT COUNT(CASE WHEN ucp.completed = true THEN 1 END)::NUMERIC * 100.0 /
     136                                  (SELECT COUNT(CASE WHEN ucp.is_completed = true THEN 1 END)::NUMERIC * 100.0 /
    133137                                          NULLIF(COUNT(*), 0)
    134138                                   FROM user_course_progress ucp
    135                                    WHERE ucp.enrollment_id = e.id), 0
     139                                   WHERE ucp.enrollment_id = e.enrollment_id), 0
    136140                          )
    137141                  ), 2)::NUMERIC AS avg_lecture_completion_percentage
    138142        FROM course c
    139                  JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'en'
    140                  JOIN course_version cv ON c.id = cv.course_id
    141                  LEFT JOIN enrollment e ON cv.id = e.course_version_id      -- left join to include versions with zero enrollments
    142                  LEFT JOIN payment p ON e.id = p.enrollment_id              -- left join to include enrollments without payments (because enrollment can be in trial)
    143                  LEFT JOIN review r ON e.id = r.enrollment_id               -- left join to include enrollments without reviews
    144         GROUP BY c.id, ct.id, cv.id
    145         HAVING COUNT(DISTINCT e.id) > 0
     143                 JOIN course_translate ct ON c.course_id = ct.course_id
     144                 JOIN language l ON l.id = ct.language_id AND l.value = 'en'
     145                 JOIN course_version cv ON c.course_id = cv.course_id
     146                 LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id   -- left join to include versions with zero enrollments
     147                 LEFT JOIN payment p ON e.enrollment_id = p.enrollment_id               -- left join to include enrollments without payments (because enrollment can be in trial)
     148                 LEFT JOIN review r ON e.enrollment_id = r.enrollment_id                -- left join to include enrollments without reviews
     149        GROUP BY c.course_id, ct.course_translate_id, cv.course_version_id
     150        HAVING COUNT(DISTINCT e.enrollment_id) > 0
    146151        ORDER BY total_revenue DESC, completion_rate_percentage DESC;
    147152END;
    … …  
    163168                      total_reviews BIGINT
    164169                  ) AS $$
     170#variable_conflict use_column
    165171BEGIN
    166172    RETURN QUERY
    167173        SELECT
    168             ex.id::INTEGER AS expert_id,
    169             ex.name::TEXT,
    170             COUNT(DISTINCT c.id)::BIGINT AS courses_created,
    171             COUNT(DISTINCT e.id)::BIGINT AS total_enrollments,
    172             COUNT(DISTINCT CASE WHEN p.payment_status = 'COMPLETED' THEN e.id END)::BIGINT AS paid_enrollments,
    173             COUNT(DISTINCT CASE WHEN e.purchase_date IS NULL THEN e.id END)::BIGINT AS trial_enrollments,
    174             COALESCE(SUM(CASE WHEN p.payment_status = 'COMPLETED' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
     174            ex.expert_id::INTEGER AS expert_id,
     175            a.name::TEXT AS expert_name,
     176            COUNT(DISTINCT c.course_id)::BIGINT AS courses_created,
     177            COUNT(DISTINCT e.enrollment_id)::BIGINT AS total_enrollments,
     178            COUNT(DISTINCT CASE WHEN p.payment_status = 'completed' THEN e.enrollment_id END)::BIGINT AS paid_enrollments,
     179            COUNT(DISTINCT CASE WHEN p.payment_id IS NULL THEN e.enrollment_id END)::BIGINT AS trial_enrollments,
     180            COALESCE(SUM(CASE WHEN p.payment_status = 'completed' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
    175181            COALESCE(AVG(r.rating), 0)::NUMERIC AS avg_rating,
    176             COUNT(r.id)::BIGINT AS total_reviews
     182            COUNT(r.review_id)::BIGINT AS total_reviews
    177183        FROM expert ex
    178                  LEFT JOIN expert_course ec ON ex.id = ec.expert_id         -- experts with no courses should be included
    179                  LEFT JOIN course c ON ec.course_id = c.id                  -- experts with no courses should be included
    180                  JOIN course_version cv ON c.id = cv.course_id              -- only courses with versions (which is always the case)
    181                  LEFT JOIN enrollment e ON cv.id = e.course_version_id      -- left join to include courses with zero enrollments
    182                  LEFT JOIN payment p ON e.id = p.enrollment_id              -- left join to include enrollments without payments
    183                  LEFT JOIN review r ON e.id = r.enrollment_id               -- left join to include enrollments without reviews
    184         GROUP BY ex.id
     184                 JOIN account a ON a.id = ex.account_id                                 -- expert name lives on account
     185                 LEFT JOIN expert_course ec ON ex.expert_id = ec.expert_id              -- experts with no courses should be included
     186                 LEFT JOIN course c ON ec.course_id = c.course_id                       -- experts with no courses should be included
     187                 LEFT JOIN course_version cv ON c.course_id = cv.course_id              -- left join so experts without courses are not dropped
     188                 LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id   -- left join to include courses with zero enrollments
     189                 LEFT JOIN payment p ON e.enrollment_id = p.enrollment_id               -- left join to include enrollments without payments
     190                 LEFT JOIN review r ON e.enrollment_id = r.enrollment_id                -- left join to include enrollments without reviews
     191        GROUP BY ex.expert_id, a.id
    185192        ORDER BY total_revenue DESC NULLS LAST;
    186193END;
    … …  
    196203      year ← YEAR(purchase_date),
    197204      month ← MONTH(purchase_date),
    198       total_enrollments ← COUNT(id),
     205      total_enrollments ← COUNT(enrollment_id),
    199206      total_revenue ← COALESCE(SUM(amount), 0) (
    200       payment ⋈ enrollment_id = id enrollment
     207      payment ⋈ enrollment_id = enrollment_id enrollment
    201208    )
    202209  )
    … …  
    206213π 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 (
    207214  τ year DESC, month DESC, total_revenue DESC, total_students DESC (
    208     γ YEAR(payment_date), MONTH(payment_date), c.id, ct.id, cv.id;
     215    γ YEAR(payment_date), MONTH(payment_date), c.course_id, ct.course_translate_id, cv.course_version_id;
    209216      year ← YEAR(payment_date),
    210217      month ← MONTH(payment_date),
    211       course_id ← c.id,
     218      course_id ← c.course_id,
    212219      course_name ← ct.title_short,
    213220      course_description ← ct.description_short,
    … …  
    215222      course_price ← c.price,
    216223      version_number ← cv.version_number,
    217       is_version_active ← cv.active,
    218       total_paid_enrollments ← COUNT(e.id),
     224      is_version_active ← cv.is_active,
     225      total_paid_enrollments ← COUNT(e.enrollment_id),
    219226      total_students ← COUNT(DISTINCT e.user_id),
    220227      total_revenue ← SUM(p.amount),
    221       total_reviews ← COUNT(r.id),
     228      total_reviews ← COUNT(r.review_id),
    222229      average_rating ← COALESCE(AVG(r.rating), 0)
    223230    (
    224       course ⋈ id = course_id ∧ language = 'en' course_translate
    225       ⋈ id = course_id course_version
    226       ⋈ id = course_version_id enrollment
    227       ⋈ id = enrollment_id ∧ payment_status = 'COMPLETED' payment
    228       ⟕ id = enrollment_id review
     231      course ⋈ course_id = course_id course_translate
     232      ⋈ language_id = id ∧ value = 'en' language
     233      ⋈ course_id = course_id course_version
     234      ⋈ course_version_id = course_version_id enrollment
     235      ⋈ enrollment_id = enrollment_id ∧ payment_status = 'completed' payment
     236      ⟕ enrollment_id = enrollment_id review
    229237    )
    230238  )
    … …  
    234242π 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 (
    235243  τ total_revenue DESC, completion_rate_percentage DESC (
    236     σ COUNT(DISTINCT e.id) > 0 (
    237       γ c.id, ct.id, cv.id;
    238         course_id ← c.id,
     244    σ COUNT(DISTINCT e.enrollment_id) > 0 (
     245      γ c.course_id, ct.course_translate_id, cv.course_version_id;
     246        course_id ← c.course_id,
    239247        course_name ← ct.title_short,
    240248        course_description ← ct.description_short,
    … …  
    242250        course_price ← c.price,
    243251        version_number ← cv.version_number,
    244         is_version_active ← cv.active,
    245         total_enrollments ← COUNT(e.id),
    246         paid_enrollments ← COUNT(CASE WHEN payment_status = 'COMPLETED' THEN e.id END),
    247         trial_enrollments ← COUNT(CASE WHEN purchase_date IS NULL THEN e.id END),
    248         total_completions ← COUNT(CASE WHEN completion_date IS NOT NULL THEN e.id END),
     252        is_version_active ← cv.is_active,
     253        total_enrollments ← COUNT(e.enrollment_id),
     254        paid_enrollments ← COUNT(CASE WHEN payment_status = 'completed' THEN e.enrollment_id END),
     255        trial_enrollments ← COUNT(CASE WHEN payment_id IS NULL THEN e.enrollment_id END),
     256        total_completions ← COUNT(CASE WHEN completion_date IS NOT NULL THEN e.enrollment_id END),
    249257        total_students ← COUNT(DISTINCT e.user_id),
    250         total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'COMPLETED' THEN amount ELSE 0 END), 0),
     258        total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'completed' THEN amount ELSE 0 END), 0),
    251259        average_rating ← COALESCE(AVG(r.rating), 0),
    252         total_reviews ← COUNT(r.id),
    253         avg_days_to_complete ← ROUND(AVG(CASE WHEN completion_date IS NOT NULL THEN EXTRACT(EPOCH FROM completion_date - activation_date) / 86400 END), 0),
    254         completion_rate_percentage ← ROUND(100.0 * COUNT(CASE WHEN completion_date IS NOT NULL THEN e.id END) / NULLIF(COUNT(e.id), 0), 2),
    255         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)
     260        total_reviews ← COUNT(r.review_id),
     261        avg_days_to_complete ← ROUND(AVG(CASE WHEN completion_date IS NOT NULL THEN completion_date - activation_date END), 0),
     262        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),
     263        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)
    256264      (
    257         course ⋈ id = course_id ∧ language = 'en' course_translate
    258         ⋈ id = course_id course_version
    259         ⟕ id = course_version_id enrollment
    260         ⟕ id = enrollment_id payment
    261         ⟕ id = enrollment_id review
     265        course ⋈ course_id = course_id course_translate
     266        ⋈ language_id = id ∧ value = 'en' language
     267        ⋈ course_id = course_id course_version
     268        ⟕ course_version_id = course_version_id enrollment
     269        ⟕ enrollment_id = enrollment_id payment
     270        ⟕ enrollment_id = enrollment_id review
    262271      )
    263272    )
    … …  
    266275
    267276-- Expert performance summary
    268 π expert_id, name, courses_created, total_enrollments, paid_enrollments, trial_enrollments, total_revenue, avg_rating, total_reviews (
     277π expert_id, expert_name, courses_created, total_enrollments, paid_enrollments, trial_enrollments, total_revenue, avg_rating, total_reviews (
    269278  τ total_revenue DESC NULLS LAST (
    270     γ ex.id;
    271       expert_id ← ex.id,
    272       name ← ex.name,
    273       courses_created ← COUNT(DISTINCT c.id),
    274       total_enrollments ← COUNT(DISTINCT e.id),
    275       paid_enrollments ← COUNT(DISTINCT CASE WHEN payment_status = 'COMPLETED' THEN e.id END),
    276       trial_enrollments ← COUNT(DISTINCT CASE WHEN purchase_date IS NULL THEN e.id END),
    277       total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'COMPLETED' THEN amount ELSE 0 END), 0),
     279    γ ex.expert_id, a.id;
     280      expert_id ← ex.expert_id,
     281      expert_name ← a.name,
     282      courses_created ← COUNT(DISTINCT c.course_id),
     283      total_enrollments ← COUNT(DISTINCT e.enrollment_id),
     284      paid_enrollments ← COUNT(DISTINCT CASE WHEN payment_status = 'completed' THEN e.enrollment_id END),
     285      trial_enrollments ← COUNT(DISTINCT CASE WHEN payment_id IS NULL THEN e.enrollment_id END),
     286      total_revenue ← COALESCE(SUM(CASE WHEN payment_status = 'completed' THEN amount ELSE 0 END), 0),
    278287      avg_rating ← COALESCE(AVG(r.rating), 0),
    279       total_reviews ← COUNT(r.id)
     288      total_reviews ← COUNT(r.review_id)
    280289    (
    281290      expert
    282       ⟕ id = expert_id expert_course
    283       ⟕ course_id = id course
    284       ⋈ id = course_id course_version
    285       ⟕ id = course_version_id enrollment
    286       ⟕ id = enrollment_id payment
    287       ⟕ id = enrollment_id review
     291      ⋈ account_id = id account
     292      ⟕ expert_id = expert_id expert_course
     293      ⟕ course_id = course_id course
     294      ⟕ course_id = course_id course_version
     295      ⟕ course_version_id = course_version_id enrollment
     296      ⟕ enrollment_id = enrollment_id payment
     297      ⟕ enrollment_id = enrollment_id review
    288298    )
    289299  )