Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

01/25/26 17:33:23 (4 weeks ago)

Author:

213140

Comment:

--

AdvancedReports

@@ -51 +51 @@
     Completion_Rate_Percentage DESC;
 }}}
+
+
+== Годишен извештај за честота на постирање блогови и број на коментари по блог кај луѓе кој имаат отидено на терапевт барем 10 пати, според тоа кој терапевт го посетуваат
+
+{{{
+WITH LoyalPatients AS (
+    SELECT
+        c.id_patient,
+        c.id_therapist,
+        u_t.name    AS therapist_name,
+        u_t.surname AS therapist_surname
+    FROM project.consultation c
+        JOIN project.therapist t
+            ON t.id_user = c.id_therapist
+        JOIN project."user" u_t
+            ON u_t.id_user = t.id_user
+    GROUP BY
+        c.id_patient,
+        c.id_therapist,
+        u_t.name,
+        u_t.surname
+    HAVING COUNT(c.id_consultation) >= 10
+),
+
+BlogStats AS (
+    SELECT
+        b.id_patient,
+        EXTRACT(YEAR FROM b.date_of_post) AS post_year,
+        COUNT(DISTINCT b.id_blog)         AS blogs_count,
+        COUNT(c.id_comment)               AS comments_count
+    FROM project.blog b
+        LEFT JOIN project.comment c
+            ON c.id_blog = b.id_blog
+    GROUP BY
+        b.id_patient,
+        EXTRACT(YEAR FROM b.date_of_post)
+ )
+
+SELECT
+    bs.post_year                                                AS "Година",
+    lp.therapist_name || ' ' || lp.therapist_surname            AS "Терапевт",
+    SUM(bs.blogs_count)                                         AS "Вкупно објавени блогови",
+    SUM(bs.comments_count)                                      AS "Вкупно примени коментари",
+    ROUND(AVG(bs.blogs_count), 2)                               AS "Просек блогови по пациент",
+    ROUND(
+        SUM(bs.comments_count)::DECIMAL
+            / NULLIF(SUM(bs.blogs_count), 0),
+        2
+    )                                                           AS "Просек коментари по блог"
+FROM LoyalPatients lp
+    JOIN BlogStats bs
+        ON bs.id_patient = lp.id_patient
+GROUP BY
+    bs.post_year,
+    lp.id_therapist,
+    lp.therapist_name,
+    lp.therapist_surname
+ORDER BY
+    bs.post_year DESC,
+    "Вкупно објавени блогови" DESC;
+}}}
+
+
+== Вкупна годишна анализа на блогови по месец во однос на внесување дневници во просек по месец
+
+{{{
+WITH Blog_Monthly AS (
+    SELECT
+        EXTRACT(YEAR FROM b.date_of_post)  AS year,
+        EXTRACT(MONTH FROM b.date_of_post) AS month,
+        COUNT(b.id_blog)                   AS total_blogs,
+        COUNT(DISTINCT b.id_patient)       AS active_bloggers
+    FROM project.blog b
+    GROUP BY 1, 2
+),
+
+Diary_Monthly AS (
+    SELECT
+        EXTRACT(YEAR FROM d.date)  AS year,
+        EXTRACT(MONTH FROM d.date) AS month,
+        COUNT(d.id_diary)          AS total_diaries,
+        COUNT(DISTINCT d.id_patient) AS active_diarists,
+        AVG(d.daily_rating)        AS avg_monthly_mood
+    FROM project.diary d
+    GROUP BY 1, 2
+),
+
+Monthly_Stats AS (
+    SELECT
+        COALESCE(bm.year, dm.year)   AS year,
+        COALESCE(bm.month, dm.month) AS month,
+        COALESCE(bm.total_blogs, 0)  AS total_blogs,
+        COALESCE(bm.active_bloggers, 0) AS active_bloggers,
+        COALESCE(dm.total_diaries, 0)   AS total_diaries,
+        COALESCE(dm.active_diarists, 0) AS active_diarists,
+        dm.avg_monthly_mood
+    FROM Blog_Monthly bm
+    FULL OUTER JOIN Diary_Monthly dm
+    ON bm.year = dm.year
+    AND bm.month = dm.month
+)
+
+SELECT
+    year  AS "Година",
+    month AS "Месец",
+
+    total_blogs   AS "Вкупно Блогови",
+    total_diaries AS "Вкупно Дневници",
+
+    ROUND(
+        total_blogs::NUMERIC / NULLIF(active_bloggers, 0),
+        2
+    ) AS "Блогови по корисник",
+
+    ROUND(
+        total_diaries::NUMERIC / NULLIF(active_diarists, 0),
+        2
+    ) AS "Дневници по корисник",
+
+    ROUND(avg_monthly_mood, 2) AS "Просечно Расположение (1-10)",
+
+    CASE
+        WHEN total_diaries > total_blogs * 2 THEN 'Highly Private (Introspective)'
+        WHEN total_blogs > total_diaries THEN 'Highly Social (Outspoken)'
+        ELSE 'Balanced'
+        END AS "Тип на Однесување",
+
+    ROUND(
+        total_blogs::NUMERIC / NULLIF(total_blogs + total_diaries, 0) * 100,
+        2
+    ) || '%' AS "Јавност (%)"
+
+FROM Monthly_Stats
+ORDER BY year DESC, month DESC;
+}}}