Changes between Initial Version and Version 1 of ComplexReports

Timestamp:

01/27/26 23:19:23 (12 days ago)

Author:

233051

Comment:

--

ComplexReports

@@ +1 @@
+= 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;
+}}}
+
+=== 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;
+}}}
+
+=== 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;
+}}}