Changes between Version 2 and Version 3 of UserLeaderboard

Timestamp:

01/29/26 03:49:49 (10 days ago)

Author:

233051

Comment:

--

UserLeaderboard

@@ -11 +11 @@
 
 {{{
+CREATE OR REPLACE FUNCTION get_report_user_reputation_leaderboard()
+    RETURNS TABLE (
+                      username TEXT,
+                      email TEXT,
+                      approved_builds_count BIGINT,
+                      total_favorites_received BIGINT,
+                      avg_rating_received NUMERIC,
+                      reputation_score NUMERIC
+                  )
+    LANGUAGE sql
+AS $$
 WITH build_stats AS (
-    SELECT 
+    SELECT
         b.id AS build_id,
         b.user_id,
@@ -18 +29 @@
         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
+             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
-)
+     user_stats AS (
+         SELECT
+             user_id,
+             COUNT(build_id) AS approved_builds_count,
+             COALESCE(SUM(favorites_count), 0) AS total_favorites_received,
+             AVG(avg_rating) AS avg_rating_received
+         FROM build_stats
+         GROUP BY user_id
+     )
 SELECT
     u.username,
@@ -37 +48 @@
     us.approved_builds_count,
     us.total_favorites_received,
-    ROUND(us.avg_rating_received, 2) AS avg_rating_received,
+    ROUND(CAST(COALESCE(us.avg_rating_received, 0) AS numeric), 2) AS avg_rating_received,
     (
         (us.approved_builds_count * 10) +
         (us.total_favorites_received * 5) +
-        (us.avg_rating_received * 20)
-    ) AS reputation_score
+        (COALESCE(us.avg_rating_received, 0) * 20)
+        ) AS reputation_score
 FROM user_stats us
-JOIN users u ON u.id = us.user_id
+         JOIN users u ON u.id = us.user_id
 ORDER BY reputation_score DESC
 LIMIT 10;
+$$;
 }}}