Complex DB Reports (SQL, Stored Procedures, Relational Algebra)

Price-to-Performance Efficiency Analysis

Report on which builds have the most computing power per dollar spent using a "price-to-performance index" calculated with the following formula:

• (CPU Cores * CPU Base Clock) + (GPU VRAM * 100)

SQL:

CREATE OR REPLACE FUNCTION get_report_price_to_performance()
    RETURNS TABLE (
                      build_name TEXT,
                      cpu_model TEXT,
                      gpu_model TEXT,
                      total_price NUMERIC,
                      performance_score NUMERIC,
                      price_to_performance_index NUMERIC
                  )
    LANGUAGE sql
AS $$
WITH cpu_per_build AS (
    SELECT
        b.id AS build_id,
        c.name AS cpu_model,
        cpu.cores,
        cpu.base_clock
    FROM build b
             JOIN build_component bc ON b.id = bc.build_id
             JOIN components c ON bc.component_id = c.id
             JOIN cpu ON c.id = cpu.component_id
    WHERE LOWER(c.type) = LOWER('CPU')
),
     gpu_per_build AS (
         SELECT
             b.id AS build_id,
             c.name AS gpu_model,
             gpu.vram
         FROM build b
                  JOIN build_component bc ON b.id = bc.build_id
                  JOIN components c ON bc.component_id = c.id
                  JOIN gpu ON c.id = gpu.component_id
         WHERE LOWER(c.type) = LOWER('GPU')
     )
SELECT
    b.name AS build_name,
    cpu.cpu_model,
    gpu.gpu_model,
    b.total_price,
    (cpu.cores * cpu.base_clock + gpu.vram * 100) AS performance_score,
    ROUND(
            CAST(
                    (cpu.cores * cpu.base_clock + gpu.vram * 100) / NULLIF(b.total_price, 0)
                AS numeric),
            4
    ) AS price_to_performance_index
FROM build b
         JOIN cpu_per_build cpu ON b.id = cpu.build_id
         JOIN gpu_per_build gpu ON b.id = gpu.build_id
WHERE b.total_price > 0
ORDER BY price_to_performance_index DESC
LIMIT 20;
$$;

Relational algebra:

cpu_per_build =
π_{b.id → build_id,
   c.name → cpu_model,
   cpu.cores,
   cpu.base_clock
} (
  σ_{c.type = 'CPU'} (
    (((build b
      ⋈_{b.id = bc.build_id} build_component bc)
      ⋈_{bc.component_id = c.id} components c)
      ⋈_{c.id = cpu.component_id} cpu)
  )
)

gpu_per_build =
π_{b.id → build_id,
   c.name → gpu_model,
   gpu.vram
} (
  σ_{c.type = 'GPU'} (
    (((build b
      ⋈_{b.id = bc.build_id} build_component bc)
      ⋈_{bc.component_id = c.id} components c)
      ⋈_{c.id = gpu.component_id} gpu)
  )
)

λ_20 (
  τ_{price_to_performance_index DESC} (
    π_{b.name → build_name,
      cpu.cpu_model,
      gpu.gpu_model,
      b.total_price,
      (cpu.cores * cpu.base_clock + gpu.vram * 100) → performance_score,
      ROUND((cpu.cores * cpu.base_clock + gpu.vram * 100), 4) → price_to_performance_index
    } (
      σ_{b.total_price > 0} (
        ((build b
          ⋈_{b.id = cpu.build_id} cpu_per_build cpu)
          ⋈_{b.id = gpu.build_id} gpu_per_build gpu)
      )
    )
  )
)