Changes between Initial Version and Version 1 of ComponentCompatibilitySuccessRate

Timestamp:

01/28/26 00:41:20 (12 days ago)

Author:

233194

Comment:

--

ComponentCompatibilitySuccessRate

@@ +1 @@
+= 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)
+    )
+  )
+)
+}}}