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