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:

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_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)) / 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(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;
$$;

Relational algebra:

storage_configs = 
  π_{b.id ⇒ build_id, 
     b.total_price,
     ssd_comp.brand ⇒ ssd_brand, 
     ssd_storage.capacity ⇒ ssd_capacity, 
     ssd_comp.price ⇒ ssd_price,
     hdd_storage.capacity ⇒ hdd_capacity, 
     hdd_comp.price ⇒ 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 ⇒ config_type
  } (
    σ_{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%')
    } (
      build b
      ⋈_{b.id = bc_ssd.build_id} build_component bc_ssd
      ⋈_{bc_ssd.component_id = ssd_comp.id} components ssd_comp
      ⋈_{ssd_storage.component_id = ssd_comp.id} storage ssd_storage
      ⟕_{b.id = bc_hdd.build_id} build_component bc_hdd
      ⟕_{bc_hdd.component_id = hdd_comp.id} components hdd_comp
      ⟕_{hdd_storage.component_id = hdd_comp.id} storage hdd_storage
    )
  )

config_performance = 
  γ_{sc.config_type, sc.ssd_brand;
     COUNT(sc.build_id) ⇒ builds_count,
     AVG(sc.ssd_price + COALESCE(sc.hdd_price, 0)) ⇒ avg_storage_cost,
     AVG(sc.ssd_capacity + COALESCE(sc.hdd_capacity, 0)) ⇒ avg_total_capacity,
     AVG(rb.value) ⇒ avg_build_rating,
     AVG((sc.ssd_price + COALESCE(sc.hdd_price, 0)) / NULLIF(sc.total_price, 0)) ⇒ storage_cost_ratio
  } (
    storage_configs sc
    ⟕_{sc.build_id = rb.build_id} rating_build rb
  )
  WHERE builds_count ≥ 2

λ_15 (
  τ_{optimization_score DESC, builds_count DESC} (
    π_{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(COALESCE(cp.avg_build_rating, 0), 2) ⇒ avg_build_rating,
       ROUND(COALESCE(cp.storage_cost_ratio, 0) * 100, 1) ⇒ storage_cost_pct,
       ROUND(
         (COALESCE(cp.avg_build_rating, 0) / 5.0 * 40) +
         (cp.avg_total_capacity / NULLIF(cp.avg_storage_cost, 0) * 0.5) +
         ((1 - COALESCE(cp.storage_cost_ratio, 0)) * 30) +
         (LN(cp.builds_count + 1) * 5),
         2
       ) ⇒ optimization_score
    } (config_performance cp)
  )
)