Component Compatibility Success Rate

Report on which component pairings (CPU + motherboard, GPU + case) have the highest build success rates based on user satisfaction and build longevity.

SQL:

CREATE OR REPLACE FUNCTION get_report_compatibility()
    RETURNS TABLE (
                      cpu_combo TEXT,
                      motherboard_chipset TEXT,
                      total_builds BIGINT,
                      avg_satisfaction NUMERIC,
                      success_rate NUMERIC
                  )
    LANGUAGE sql
AS $$
WITH cpu_mobo_pairs AS (
    SELECT
        cpu_comp.brand AS cpu_brand,
        cpu_comp.name AS cpu_model,
        mobo.chipset AS motherboard_chipset,
        b.id AS build_id,
        b.user_id,
        b.created_at
    FROM build b
             JOIN build_component bc_cpu ON b.id = bc_cpu.build_id
             JOIN components cpu_comp ON bc_cpu.component_id = cpu_comp.id
             JOIN cpu ON cpu.component_id = cpu_comp.id
             JOIN build_component bc_mobo ON b.id = bc_mobo.build_id
             JOIN components mobo_comp ON bc_mobo.component_id = mobo_comp.id
             JOIN motherboard mobo ON mobo.component_id = mobo_comp.id
    WHERE b.is_approved = TRUE
),
     recent_activity AS (
         SELECT DISTINCT build_id
         FROM review
         WHERE created_at >= CURRENT_DATE - INTERVAL '3 months'
     ),
     pair_metrics AS (
         SELECT
             cmp.cpu_brand,
             cmp.cpu_model,
             cmp.motherboard_chipset,
             COUNT(DISTINCT cmp.build_id) AS total_builds,
             AVG(COALESCE(rb.value, 0)) AS avg_satisfaction,
             COUNT(DISTINCT ra.build_id) AS active_builds
         FROM cpu_mobo_pairs cmp
                  LEFT JOIN rating_build rb ON cmp.build_id = rb.build_id
                  LEFT JOIN recent_activity ra ON cmp.build_id = ra.build_id
         GROUP BY
             cmp.cpu_brand,
             cmp.cpu_model,
             cmp.motherboard_chipset
         HAVING COUNT(DISTINCT cmp.build_id) >= 2
     )
SELECT
    CONCAT(cpu_brand, ' ', cpu_model) AS cpu_combo,
    motherboard_chipset,
    total_builds,
    ROUND(CAST(avg_satisfaction AS numeric), 2) AS avg_satisfaction,
    ROUND(
            CAST(
                    (avg_satisfaction / 5.0) * 0.6 +
                    (CAST(active_builds AS DECIMAL) / total_builds) * 0.4
                AS numeric),
            3
    ) AS success_rate
FROM pair_metrics
ORDER BY success_rate DESC, total_builds DESC
LIMIT 15;
$$;

Relational algebra:

cpu_mobo_pairs = 
  π_{cpu_comp.brand ⇒ cpu_brand, 
     cpu_comp.name ⇒ cpu_model,
     mobo.chipset ⇒ motherboard_chipset,
     b.id ⇒ build_id,
     b.user_id,
     b.created_at
  } (
    σ_{b.is_approved = TRUE} (
      build b 
      ⋈_{b.id = bc_cpu.build_id} build_component bc_cpu
      ⋈_{bc_cpu.component_id = cpu_comp.id} components cpu_comp
      ⋈_{cpu.component_id = cpu_comp.id} cpu
      ⋈_{b.id = bc_mobo.build_id} build_component bc_mobo
      ⋈_{bc_mobo.component_id = mobo_comp.id} components mobo_comp
      ⋈_{mobo.component_id = mobo_comp.id} motherboard mobo
    )
  )

recent_activity = 
  π_{DISTINCT build_id} (
    σ_{created_at ≥ CURRENT_DATE - INTERVAL '3 months'} (review)
  )

pair_metrics = 
  γ_{cmp.cpu_brand, cmp.cpu_model, cmp.motherboard_chipset;
     COUNT(DISTINCT cmp.build_id) ⇒ total_builds,
     AVG(COALESCE(rb.value, 0)) ⇒ avg_satisfaction,
     COUNT(DISTINCT ra.build_id) ⇒ active_builds
  } (
    cpu_mobo_pairs cmp
    ⟕_{cmp.build_id = rb.build_id} rating_build rb
    ⟕_{cmp.build_id = ra.build_id} recent_activity ra
  )
  WHERE total_builds ≥ 2

λ_15 (
  τ_{success_rate DESC, total_builds DESC} (
    π_{CONCAT(pm.cpu_brand, ' ', pm.cpu_model) ⇒ cpu_combo,
       pm.motherboard_chipset,
       pm.total_builds,
       ROUND(pm.avg_satisfaction, 2) ⇒ avg_satisfaction,
       ROUND((pm.avg_satisfaction / 5.0) * 0.6 + (pm.active_builds / pm.total_builds) * 0.4, 3) ⇒ success_rate
    } (pair_metrics pm)
  )
)