Changes between Initial Version and Version 1 of UserLeaderboard

Timestamp:

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

Author:

233051

Comment:

--

UserLeaderboard

@@ +1 @@
+= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
+
+=== 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
+    )
+  )
+)
+}}}