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

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
LEFT JOIN project.diary d 
    ON d.id_patient = p.id_user
GROUP BY EXTRACT(YEAR FROM d.date)
ORDER BY Year;