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

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;