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)
    )
   )
  )
 )
)