Changes between Version 1 and Version 2 of ComponentCompatibilitySuccessRate

Timestamp:

01/29/26 03:51:25 (10 days ago)

Author:

233051

Comment:

--

ComponentCompatibilitySuccessRate

@@ -8 +8 @@
 
 {{{
+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.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
+    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
 ),
-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
-)
+     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(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
+    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 12;
+LIMIT 15;
+$$;
 }}}