Changes between Version 2 and Version 3 of TopPerformingComponents

Timestamp:

01/29/26 03:49:07 (10 days ago)

Author:

233051

Comment:

--

TopPerformingComponents

@@ -1 @@
-= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
-
-===  Top Performing Components in Highly-Rated Builds ===
+==  Top Performing Components in Highly-Rated Builds ==
 
 Report on which specific components appear most frequently in builds that have received a high average user rating (4.5 stars or higher) within the last year.
@@ -8 +6 @@
 
 {{{
-SELECT 
+CREATE OR REPLACE FUNCTION get_report_top_components()
+    RETURNS TABLE (
+                      type TEXT,
+                      brand TEXT,
+                      name TEXT,
+                      usage_count BIGINT,
+                      avg_build_rating NUMERIC
+                  )
+    LANGUAGE sql
+AS $$
+SELECT
     c.type,
     c.brand,
@@ -15 +23 @@
     AVG(rb.value) AS avg_build_rating
 FROM components c
-JOIN build_component bc ON c.id = bc.component_id
-JOIN build b ON bc.build_id = b.id
-JOIN rating_build rb ON b.id = rb.build_id
+         JOIN build_component bc ON c.id = bc.component_id
+         JOIN build b ON bc.build_id = b.id
+         JOIN rating_build rb ON b.id = rb.build_id
 WHERE b.created_at >= CURRENT_DATE - INTERVAL '1 year'
 GROUP BY c.type, c.brand, c.name
-HAVING avg_build_rating >= 4.5 
+HAVING AVG(rb.value) >= 4.5
 ORDER BY usage_count DESC, avg_build_rating DESC
-LIMIT 20;
+LIMIT 15;
+$$;
 }}}