Вкупна годишна анализа од пациенти - расположение, успешност на консултации и терапија

SELECT
    EXTRACT(YEAR FROM d.date) AS Year,
    COUNT(DISTINCT p.id_user) AS Total_Patients,
    COUNT(DISTINCT c.id_consultation) AS Total_Consultations,
    COUNT(DISTINCT th.id_therapy) AS Total_Therapies,
    ROUND(AVG(d.daily_rating), 2) AS Avg_Mood_All_Patients,
    MIN(d.daily_rating) AS Lowest_Mood_Recorded
FROM project.patient p
JOIN project.user u 
    ON u.id_user = p.id_user
LEFT JOIN project.consultation c 
    ON c.id_patient = p.id_user
LEFT JOIN project.therapy th 
    ON th.id_consultation = c.id_consultation
JOIN project.diary d                     
    ON d.id_patient = p.id_user
GROUP BY EXTRACT(YEAR FROM d.date)
ORDER BY Year;

Годишен извештај за честота на користење на консултации и процент на платени консултации заедно со нивната просечна цена

SELECT
    EXTRACT(YEAR FROM c.date) AS Year,
    t.id_user,
    u.name || ' ' || u.surname AS Therapist_Name,
    COUNT(c.id_consultation) AS Total_Consultations,
    COUNT(CASE WHEN date_of_payment IS NOT NULL THEN 1 END) AS Completed_Consultations,
    ROUND(
            COUNT(CASE WHEN date_of_payment IS NOT NULL THEN 1 END)::DECIMAL
                / NULLIF(COUNT(c.id_consultation), 0) * 100,
            2
    ) AS Completion_Rate_Percentage,
    ROUND(AVG(c.price), 2) AS Avg_Consultation_Price
FROM project.therapist t
         JOIN project.user u
              ON u.id_user = t.id_user
         JOIN project.consultation c
              ON c.id_therapist = t.id_user
GROUP BY
    EXTRACT(YEAR FROM c.date),
    t.id_user,
    u.name,
    u.surname
ORDER BY
    Year,
    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;