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.brand ⇒ cpu_brand, cpu.name ⇒ cpu_model,
     mobo.chipset ⇒ motherboard_chipset,
     b.id ⇒ build_id
  } (
    σ_{c1.type = 'CPU' AND c2.type = 'Motherboard'} (
      build b 
      ⋈_{b.id = bc1.build_id} build_component bc1
      ⋈_{bc1.component_id = c1.id} components c1
      ⋈_{b.id = bc2.build_id} build_component bc2
      ⋈_{bc2.component_id = c2.id} components c2
    )
  )

pair_metrics = 
  γ_{cpu_brand, cpu_model, motherboard_chipset;
     COUNT(build_id) ⇒ total_builds,
     AVG(rb.value) ⇒ avg_satisfaction,
     COUNT(DISTINCT ub.user_id WHERE ub.still_active = TRUE) ⇒ active_builds
  } (
    cpu_mobo_pairs cmp
    ⋈_{cmp.build_id = rb.build_id} rating_build rb
    ⋈_{cmp.build_id = ub.build_id} user_builds ub
  )

λ_12 (
  τ_{success_rate DESC, total_builds DESC} (
    σ_{total_builds ≥ 10} (
      π_{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)
    )
  )
)