Complex DB Reports (SQL, Stored Procedures, Relational Algebra)

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:

WITH cpu_mobo_pairs AS (
  SELECT
    cpu.brand AS cpu_brand,
    cpu.name AS cpu_model,
    mobo.chipset AS motherboard_chipset,
    b.id AS build_id,
    b.user_id
  FROM build b
  JOIN build_component bc_cpu ON b.id = bc_cpu.build_id
  JOIN components cpu ON bc_cpu.component_id = cpu.id
  JOIN build_component bc_mobo ON b.id = bc_mobo.build_id
  JOIN components mobo ON bc_mobo.component_id = mobo.id
  WHERE cpu.type = 'CPU' 
    AND mobo.type = 'Motherboard'
    AND b.is_approved = TRUE
),
pair_metrics AS (
  SELECT
    cmp.cpu_brand,
    cmp.cpu_model,
    cmp.motherboard_chipset,
    COUNT(cmp.build_id) AS total_builds,
    AVG(rb.value) AS avg_satisfaction,
    COUNT(DISTINCT CASE WHEN ub.last_active >= CURRENT_DATE - INTERVAL '3 months' 
          THEN cmp.build_id END) AS active_builds
  FROM cpu_mobo_pairs cmp
  LEFT JOIN rating_build rb ON cmp.build_id = rb.build_id
  LEFT JOIN user_builds ub ON cmp.build_id = ub.build_id
  GROUP BY cmp.cpu_brand, cmp.cpu_model, cmp.motherboard_chipset
  HAVING COUNT(cmp.build_id) >= 10
)
SELECT
  CONCAT(cpu_brand, ' ', cpu_model) AS cpu_combo,
  motherboard_chipset,
  total_builds,
  ROUND(avg_satisfaction, 2) AS avg_satisfaction,
  ROUND(
    (avg_satisfaction / 5.0) * 0.6 + 
    (CAST(active_builds AS DECIMAL) / total_builds) * 0.4, 
    3
  ) AS success_rate
FROM pair_metrics
ORDER BY success_rate DESC, total_builds DESC
LIMIT 12;

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