Changes between Initial Version and Version 1 of PricetoPerformance

Timestamp:

01/27/26 23:49:20 (12 days ago)

Author:

233051

Comment:

--

PricetoPerformance

@@ +1 @@
+= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
+
+=== Price-to-Performance Efficiency Analysis ===
+
+Report on which builds have the most computing power per dollar spent using a "price-to-performance index" calculated with the following formula:
+* (CPU Cores * CPU Base Clock) + (GPU VRAM * 100)
+
+**SQL:**
+
+{{{
+WITH cpu_per_build AS (
+    SELECT 
+        b.id AS build_id,
+        c.name AS cpu_model,
+        cpu.cores,
+        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'
+),
+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 
+    b.name AS build_name,
+    cpu.cpu_model,
+    gpu.gpu_model,
+    b.total_price,
+    (cpu.cores * cpu.base_clock + gpu.vram * 100) AS performance_score,
+    ROUND(
+        (cpu.cores * cpu.base_clock + gpu.vram * 100), 
+        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
+WHERE b.total_price > 0
+ORDER BY price_to_performance_index DESC
+LIMIT 20;
+}}}
+
+**Relational algebra:**
+
+{{{
+cpu_per_build =
+π_{b.id → build_id,
+   c.name → cpu_model,
+   cpu.cores,
+   cpu.base_clock
+} (
+  σ_{c.type = 'CPU'} (
+    (((build b
+      ⋈_{b.id = bc.build_id} build_component bc)
+      ⋈_{bc.component_id = c.id} components c)
+      ⋈_{c.id = cpu.component_id} cpu)
+  )
+)
+
+gpu_per_build =
+π_{b.id → build_id,
+   c.name → gpu_model,
+   gpu.vram
+} (
+  σ_{c.type = 'GPU'} (
+    (((build b
+      ⋈_{b.id = bc.build_id} build_component bc)
+      ⋈_{bc.component_id = c.id} components c)
+      ⋈_{c.id = gpu.component_id} gpu)
+  )
+)
+
+λ_20 (
+  τ_{price_to_performance_index DESC} (
+    π_{b.name → build_name,
+      cpu.cpu_model,
+      gpu.gpu_model,
+      b.total_price,
+      (cpu.cores * cpu.base_clock + gpu.vram * 100) → performance_score,
+      ROUND((cpu.cores * cpu.base_clock + gpu.vram * 100), 4) → price_to_performance_index
+    } (
+      σ_{b.total_price > 0} (
+        ((build b
+          ⋈_{b.id = cpu.build_id} cpu_per_build cpu)
+          ⋈_{b.id = gpu.build_id} gpu_per_build gpu)
+      )
+    )
+  )
+)
+}}}