Changes between Version 1 and Version 2 of PricetoPerformance

Timestamp:

01/29/26 03:50:23 (10 days ago)

Author:

233051

Comment:

--

PricetoPerformance

@@ -9 +9 @@
 
 {{{
+CREATE OR REPLACE FUNCTION get_report_price_to_performance()
+    RETURNS TABLE (
+                      build_name TEXT,
+                      cpu_model TEXT,
+                      gpu_model TEXT,
+                      total_price NUMERIC,
+                      performance_score NUMERIC,
+                      price_to_performance_index NUMERIC
+                  )
+    LANGUAGE sql
+AS $$
 WITH cpu_per_build AS (
-    SELECT 
+    SELECT
         b.id AS build_id,
         c.name AS cpu_model,
@@ -16 +27 @@
         cpu.base_clock
     FROM build b
-    JOIN build_component bc ON b.id = bc.build_id
-    JOIN components c ON bc.component_id = c.id
-    JOIN cpu ON c.id = cpu.component_id
-    WHERE c.type = 'CPU'
+             JOIN build_component bc ON b.id = bc.build_id
+             JOIN components c ON bc.component_id = c.id
+             JOIN cpu ON c.id = cpu.component_id
+    WHERE LOWER(c.type) = LOWER('CPU')
 ),
-gpu_per_build AS (
-    SELECT 
-        b.id AS build_id,
-        c.name AS gpu_model,
-        gpu.vram
-    FROM build b
-    JOIN build_component bc ON b.id = bc.build_id
-    JOIN components c ON bc.component_id = c.id
-    JOIN gpu ON c.id = gpu.component_id
-    WHERE c.type = 'GPU'
-)
-
-SELECT 
+     gpu_per_build AS (
+         SELECT
+             b.id AS build_id,
+             c.name AS gpu_model,
+             gpu.vram
+         FROM build b
+                  JOIN build_component bc ON b.id = bc.build_id
+                  JOIN components c ON bc.component_id = c.id
+                  JOIN gpu ON c.id = gpu.component_id
+         WHERE LOWER(c.type) = LOWER('GPU')
+     )
+SELECT
     b.name AS build_name,
     cpu.cpu_model,
@@ -40 +50 @@
     (cpu.cores * cpu.base_clock + gpu.vram * 100) AS performance_score,
     ROUND(
-        (cpu.cores * cpu.base_clock + gpu.vram * 100), 
-        4
+            CAST(
+                    (cpu.cores * cpu.base_clock + gpu.vram * 100) / NULLIF(b.total_price, 0)
+                AS numeric),
+            4
     ) AS price_to_performance_index
 FROM build b
-JOIN cpu_per_build cpu ON b.id = cpu.build_id
-JOIN gpu_per_build gpu ON b.id = gpu.build_id
+         JOIN cpu_per_build cpu ON b.id = cpu.build_id
+         JOIN gpu_per_build gpu ON b.id = gpu.build_id
 WHERE b.total_price > 0
 ORDER BY price_to_performance_index DESC
 LIMIT 20;
+$$;
 }}}