Changes between Initial Version and Version 1 of StorageConfigurationOptimizationScore

Timestamp:

01/28/26 00:44:56 (12 days ago)

Author:

233194

Comment:

--

StorageConfigurationOptimizationScore

@@ +1 @@
+= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
+
+===  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.
+
+**SQL:**
+
+{{{
+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'
+),
+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
+)
+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
+FROM config_performance
+ORDER BY optimization_score DESC, builds_count DESC
+LIMIT 15;
+}}}
+
+**Relational algebra:**
+
+{{{
+storage_configs = 
+  π_{b.id ⇒ build_id, b.total_price,
+     ssd.brand ⇒ ssd_brand, ssd.capacity ⇒ ssd_capacity, ssd.price ⇒ ssd_price,
+     hdd.capacity ⇒ hdd_capacity, hdd.price ⇒ 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 ⇒ config_type
+  } (
+    σ_{ssd_comp.type = 'SSD' AND (hdd_comp.type = 'HDD' OR hdd_comp.type IS NULL)} (
+      build b
+      ⋈_{b.id = bc_ssd.build_id} build_component bc_ssd
+      ⋈_{bc_ssd.component_id = ssd_comp.id} components ssd_comp
+      ⟕_{b.id = bc_hdd.build_id AND bc_hdd.component_id = hdd_comp.id AND hdd_comp.type = 'HDD'} 
+        (build_component bc_hdd ⋈ components hdd_comp)
+    )
+  )
+
+config_performance = 
+  γ_{config_type, ssd_brand;
+     COUNT(build_id) ⇒ builds_count,
+     AVG(ssd_price + COALESCE(hdd_price, 0)) ⇒ avg_storage_cost,
+     AVG(ssd_capacity + COALESCE(hdd_capacity, 0)) ⇒ avg_total_capacity,
+     AVG(rb.value) ⇒ avg_build_rating,
+     AVG((ssd_price + COALESCE(hdd_price, 0)) / total_price) ⇒ storage_cost_ratio
+  } (
+    σ_{b.is_approved = TRUE AND b.created_at ≥ CURRENT_DATE - INTERVAL '1 year'} (
+      storage_configs sc ⋈_{sc.build_id = b.id} build b
+      ⋈_{sc.build_id = rb.build_id} rating_build rb
+    )
+  )
+
+λ_15 (
+  τ_{optimization_score DESC, builds_count DESC} (
+    σ_{builds_count ≥ 5} (
+      π_{cp.config_type,
+         cp.ssd_brand,
+         cp.builds_count,
+         ROUND(cp.avg_storage_cost, 2) ⇒ avg_storage_cost,
+         ROUND(cp.avg_total_capacity, 0) ⇒ avg_total_capacity_gb,
+         ROUND(cp.avg_build_rating, 2) ⇒ avg_build_rating,
+         ROUND(cp.storage_cost_ratio * 100, 1) ⇒ storage_cost_pct,
+         ROUND(
+           (cp.avg_build_rating / 5.0 * 40) +
+           (cp.avg_total_capacity / cp.avg_storage_cost * 0.5) +
+           ((1 - cp.storage_cost_ratio) * 30) +
+           (LOG(cp.builds_count) * 5),
+           2
+         ) ⇒ optimization_score
+      } (config_performance cp)
+    )
+  )
+)
+}}}