Changes between Version 1 and Version 2 of StorageConfigurationOptimizationScore

Timestamp:

01/29/26 03:52:02 (10 days ago)

Author:

233051

Comment:

--

StorageConfigurationOptimizationScore

@@ -1 @@
-= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
-
-===  Storage Configuration Optimization Score ===
+==  Storage Configuration Optimization Score ==
 
 Report on which storage configurations (SSD + HDD combinations) provide the best balance between speed, reliability, cost-efficiency, failure rate and user approval within different build price ranges.
@@ -8 +6 @@
 
 {{{
+CREATE OR REPLACE FUNCTION get_report_storage_optimization()
+    RETURNS TABLE (
+                      config_type TEXT,
+                      ssd_brand TEXT,
+                      builds_count BIGINT,
+                      avg_storage_cost NUMERIC,
+                      avg_total_capacity_gb NUMERIC,
+                      avg_build_rating NUMERIC,
+                      storage_cost_pct NUMERIC,
+                      optimization_score NUMERIC
+                  )
+    LANGUAGE sql
+AS $$
 WITH storage_configs AS (
-  SELECT
-    b.id AS build_id,
-    b.total_price,
-    ssd.brand AS ssd_brand,
-    ssd.capacity AS ssd_capacity,
-    ssd.price AS ssd_price,
-    hdd.capacity AS hdd_capacity,
-    hdd.price AS hdd_price,
-    CASE 
-      WHEN hdd.capacity IS NULL THEN 'SSD-Only'
-      WHEN ssd.capacity < 512 THEN 'SSD-Boot-HDD-Storage'
-      ELSE 'SSD-Primary-HDD-Archive'
-    END AS config_type
-  FROM build b
-  JOIN build_component bc_ssd ON b.id = bc_ssd.build_id
-  JOIN components ssd ON bc_ssd.component_id = ssd.id
-  LEFT JOIN build_component bc_hdd ON b.id = bc_hdd.build_id
-  LEFT JOIN components hdd ON bc_hdd.component_id = hdd.id AND hdd.type = 'HDD'
-  WHERE ssd.type = 'SSD'
-    AND b.is_approved = TRUE
-    AND b.created_at >= CURRENT_DATE - INTERVAL '1 year'
+    SELECT
+        b.id AS build_id,
+        b.total_price,
+
+        ssd_comp.brand AS ssd_brand,
+        ssd_storage.capacity AS ssd_capacity,
+        ssd_comp.price AS ssd_price,
+
+        hdd_storage.capacity AS hdd_capacity,
+        hdd_comp.price AS hdd_price,
+
+        CASE
+            WHEN hdd_storage.capacity IS NULL THEN 'SSD-Only'
+            WHEN ssd_storage.capacity < 512 THEN 'SSD-Boot-HDD-Storage'
+            ELSE 'SSD-Primary-HDD-Archive'
+            END AS config_type
+    FROM build b
+             JOIN build_component bc_ssd ON b.id = bc_ssd.build_id
+             JOIN components ssd_comp ON bc_ssd.component_id = ssd_comp.id
+             JOIN storage ssd_storage ON ssd_storage.component_id = ssd_comp.id
+
+             LEFT JOIN build_component bc_hdd ON b.id = bc_hdd.build_id
+             LEFT JOIN components hdd_comp ON bc_hdd.component_id = hdd_comp.id
+             LEFT JOIN storage hdd_storage ON hdd_storage.component_id = hdd_comp.id
+
+    WHERE ssd_comp.type = 'storage'
+      AND b.is_approved = TRUE
+      AND b.created_at >= CURRENT_DATE - INTERVAL '1 year'
+
+      AND (
+        ssd_storage.type ILIKE '%nvme%'
+            OR ssd_storage.type ILIKE '%ssd%'
+            OR ssd_storage.type ILIKE '%m.2%'
+            OR ssd_storage.form_factor ILIKE '%m.2%'
+        )
+      AND (
+        hdd_storage.component_id IS NULL
+            OR hdd_storage.type ILIKE '%hdd%'
+            OR hdd_storage.form_factor ILIKE '%3.5%'
+        )
 ),
-config_performance AS (
-  SELECT
-    sc.config_type,
-    sc.ssd_brand,
-    COUNT(sc.build_id) AS builds_count,
-    AVG(sc.ssd_price + COALESCE(sc.hdd_price, 0)) AS avg_storage_cost,
-    AVG(sc.ssd_capacity + COALESCE(sc.hdd_capacity, 0)) AS avg_total_capacity,
-    AVG(rb.value) AS avg_build_rating,
-    AVG((sc.ssd_price + COALESCE(sc.hdd_price, 0)) / sc.total_price) AS storage_cost_ratio
-  FROM storage_configs sc
-  LEFT JOIN rating_build rb ON sc.build_id = rb.build_id
-  GROUP BY sc.config_type, sc.ssd_brand
-  HAVING COUNT(sc.build_id) >= 5
-)
+     config_performance AS (
+         SELECT
+             sc.config_type,
+             sc.ssd_brand,
+             COUNT(sc.build_id) AS builds_count,
+             AVG(sc.ssd_price + COALESCE(sc.hdd_price, 0)) AS avg_storage_cost,
+             AVG(sc.ssd_capacity + COALESCE(sc.hdd_capacity, 0)) AS avg_total_capacity,
+             AVG(rb.value) AS avg_build_rating,
+             AVG((sc.ssd_price + COALESCE(sc.hdd_price, 0)) / NULLIF(sc.total_price, 0)) AS storage_cost_ratio
+         FROM storage_configs sc
+                  LEFT JOIN rating_build rb ON sc.build_id = rb.build_id
+         GROUP BY sc.config_type, sc.ssd_brand
+         HAVING COUNT(sc.build_id) >= 2
+     )
 SELECT
-  config_type,
-  ssd_brand,
-  builds_count,
-  ROUND(avg_storage_cost, 2) AS avg_storage_cost,
-  ROUND(avg_total_capacity, 0) AS avg_total_capacity_gb,
-  ROUND(avg_build_rating, 2) AS avg_build_rating,
-  ROUND(storage_cost_ratio * 100, 1) AS storage_cost_pct,
-  ROUND(
-    (avg_build_rating / 5.0 * 40) +
-    (avg_total_capacity / avg_storage_cost * 0.5) +
-    ((1 - storage_cost_ratio) * 30) +
-    (LN(builds_count) * 5),
-    2
-  ) AS optimization_score
+    config_type,
+    ssd_brand,
+    builds_count,
+    ROUND(CAST(avg_storage_cost AS numeric), 2) AS avg_storage_cost,
+    ROUND(CAST(avg_total_capacity AS numeric), 0) AS avg_total_capacity_gb,
+    ROUND(CAST(COALESCE(avg_build_rating, 0) AS numeric), 2) AS avg_build_rating,
+    ROUND(CAST(COALESCE(storage_cost_ratio, 0) * 100 AS numeric), 1) AS storage_cost_pct,
+    ROUND(
+            CAST(
+                    (COALESCE(avg_build_rating, 0) / 5.0 * 40) +
+                    (avg_total_capacity / NULLIF(avg_storage_cost, 0) * 0.5) +
+                    ((1 - COALESCE(storage_cost_ratio, 0)) * 30) +
+                    (LN(CAST(builds_count + 1 AS numeric)) * 5)
+                AS numeric),
+            2
+    ) AS optimization_score
 FROM config_performance
 ORDER BY optimization_score DESC, builds_count DESC
 LIMIT 15;
+$$;
 }}}