Changes between Initial Version and Version 1 of TopPerformingComponents

Timestamp:

01/27/26 23:46:27 (12 days ago)

Author:

233051

Comment:

--

TopPerformingComponents

@@ +1 @@
+= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
+
+===  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.
+
+SQL: 
+
+{{{
+SELECT 
+    c.type,
+    c.brand,
+    c.name,
+    COUNT(bc.component_id) AS usage_count,
+    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
+WHERE b.created_at >= CURRENT_DATE - INTERVAL '1 year'
+GROUP BY c.type, c.brand, c.name
+HAVING avg_build_rating >= 4.5 
+ORDER BY usage_count DESC, avg_build_rating DESC
+LIMIT 20;
+}}}
+
+Relational algebra:
+
+{{{
+λ_20(
+ τ_{usage_count DESC, avg_build_rating DESC}(
+  σ_{avg_build_rating ≥ 4.5}(
+   γ_{c.type, c.brand, c.name;
+     COUNT(bc.component_id)→usage_count,
+     AVG(rb.value)→avg_build_rating
+   }(
+    σ_{b.created_at ≥ CURRENT_DATE - 1 year}(
+     (((components c ⋈_{c.id=bc.component_id} build_component bc)
+       ⋈_{bc.build_id=b.id} build b)
+       ⋈_{b.id=rb.build_id} rating_build rb)
+    )
+   )
+  )
+ )
+)
+}}}