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.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)
    )
  )
)