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

SQL:

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
        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,
             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,
    u.email,
    us.approved_builds_count,
    us.total_favorites_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) +
        (COALESCE(us.avg_rating_received, 0) * 20)
        ) AS reputation_score
FROM user_stats us
         JOIN users u ON u.id = us.user_id
ORDER BY reputation_score DESC
LIMIT 10;
$$;

Relational algebra:

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