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

Top Performing Components in Highly-Rated Builds

Report on which specific components appear most frequently in builds that have received a high average user rating (4.5 stars or higher) within the last year.

SELECT 
    c.type,
    c.brand,
    c.name,
    COUNT(bc.component_id) AS usage_count,
    AVG(rb.value) AS avg_build_rating
FROM components c
JOIN build_component bc ON c.id = bc.component_id
JOIN build b ON bc.build_id = b.id
JOIN rating_build rb ON b.id = rb.build_id
WHERE b.created_at >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY c.type, c.brand, c.name
HAVING avg_build_rating >= 4.5 
ORDER BY usage_count DESC, avg_build_rating DESC
LIMIT 20;

λ_20(
 τ_{usage_count DESC, avg_build_rating DESC}(
  σ_{avg_build_rating ≥ 4.5}(
   γ_{c.type, c.brand, c.name;
     COUNT(bc.component_id)→usage_count,
     AVG(rb.value)→avg_build_rating
   }(
    σ_{b.created_at ≥ CURRENT_DATE - 1 year}(
     (((components c ⋈_{c.id=bc.component_id} build_component bc)
       ⋈_{bc.build_id=b.id} build b)
       ⋈_{b.id=rb.build_id} rating_build rb)
    )
   )
  )
 )
)

User Reputation Leaderboard

Report on the most valuable forgers on PCForge. The score is calculated using the following metrics:

• Productivity: Number of approved builds created (Weight: 10).
• Popularity: Number of times their builds were favourited (Weight: 5).
• Quality: The average star rating across all their builds (Weight: 20).

WITH build_stats AS (
    SELECT 
        b.id AS build_id,
        b.user_id,
        COUNT(DISTINCT fb.user_id) AS favorites_count,
        AVG(rb.value) AS avg_rating
    FROM build b
    LEFT JOIN favorite_build fb ON b.id = fb.build_id
    LEFT JOIN rating_build rb ON b.id = rb.build_id
    WHERE b.is_approved = TRUE
    GROUP BY b.id, b.user_id
),
user_stats AS (
    SELECT
        user_id,
        COUNT(build_id) AS approved_builds_count,
        SUM(favorites_count) AS total_favorites_received,
        AVG(avg_rating) AS avg_rating_received
    FROM build_stats
    GROUP BY user_id
)
SELECT
    u.username,
    u.email,
    us.approved_builds_count,
    us.total_favorites_received,
    ROUND(us.avg_rating_received, 2) AS avg_rating_received,
    (
        (us.approved_builds_count * 10) +
        (us.total_favorites_received * 5) +
        (us.avg_rating_received * 20)
    ) AS reputation_score
FROM user_stats us
JOIN users u ON u.id = us.user_id
ORDER BY reputation_score DESC
LIMIT 10;

build_stats =
γ_{b.id → build_id, b.user_id;
   COUNT_DISTINCT(fb.user_id) → favorites_count,
   AVG(rb.value) → avg_rating
} (
  σ_{b.is_approved = TRUE} (
    (build b
      ⟕_{b.id = fb.build_id} favorite_build fb)
      ⟕_{b.id = rb.build_id} rating_build rb
  )
)

user_stats =
γ_{user_id;
COUNT(build_id) → approved_builds_count,
SUM(favorites_count) → total_favorites_received,
AVG(avg_rating) → avg_rating_received
} (
build_stats
)

λ_10 (
  τ_{reputation_score DESC} (
    π_{u.username,
      u.email,
      us.approved_builds_count,
      us.total_favorites_received,
      ROUND(us.avg_rating_received, 2) → avg_rating_received,
      ((us.approved_builds_count * 10)
       + (us.total_favorites_received * 5)
       + (us.avg_rating_received * 20)) → reputation_score
    } (
      user_stats us ⋈_{u.id = us.user_id} users u
    )
  )
)

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)

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 c.type = '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 c.type = '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(
        (cpu.cores * cpu.base_clock + gpu.vram * 100), 
        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;

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