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:

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,
    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(rb.value) >= 4.5
ORDER BY usage_count DESC, avg_build_rating DESC
LIMIT 15;
$$;

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