Changes between Version 5 and Version 6 of AdvancedQueries

Timestamp:

08/27/25 10:41:42 (2 months ago)

Author:

221007

Comment:

--

AdvancedQueries

@@ -310 +310 @@
 order by matching_diagnoses_count desc;
 }}}
+
+=== Пребарување на најдобрите 3 институции во изминатиот период од 1 година
+
+{{{
+WITH top_3_institutions AS (
+  SELECT ar.institution_id, COUNT(*) AS total_reports
+  FROM report r
+  JOIN academicreport ar ON ar.report_id = r.report_id
+  WHERE r.created_at >= date_trunc('year', now()) - interval '1 year'
+  GROUP BY ar.institution_id
+  ORDER BY COUNT(*) DESC
+  LIMIT 3
+)
+SELECT i.name, a.total_reports
+FROM top_3_institutions a
+JOIN institution i ON i.institution_id = a.institution_id
+ORDER BY a.total_reports DESC;
+}}}