| 8 | | [wiki:UserRegistration User registration] \\ |
| 9 | | |
| 10 | | === Top Performing Components in Highly-Rated Builds === |
| 11 | | |
| 12 | | 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. |
| 13 | | |
| 14 | | {{{ |
| 15 | | SELECT |
| 16 | | c.type, |
| 17 | | c.brand, |
| 18 | | c.name, |
| 19 | | COUNT(bc.component_id) AS usage_count, |
| 20 | | AVG(rb.value) AS avg_build_rating |
| 21 | | FROM components c |
| 22 | | JOIN build_component bc ON c.id = bc.component_id |
| 23 | | JOIN build b ON bc.build_id = b.id |
| 24 | | JOIN rating_build rb ON b.id = rb.build_id |
| 25 | | WHERE b.created_at >= CURRENT_DATE - INTERVAL '1 year' |
| 26 | | GROUP BY c.type, c.brand, c.name |
| 27 | | HAVING avg_build_rating >= 4.5 |
| 28 | | ORDER BY usage_count DESC, avg_build_rating DESC |
| 29 | | LIMIT 20; |
| 30 | | }}} |
| 31 | | |
| 32 | | {{{ |
| 33 | | λ_20( |
| 34 | | τ_{usage_count DESC, avg_build_rating DESC}( |
| 35 | | σ_{avg_build_rating ≥ 4.5}( |
| 36 | | γ_{c.type, c.brand, c.name; |
| 37 | | COUNT(bc.component_id)→usage_count, |
| 38 | | AVG(rb.value)→avg_build_rating |
| 39 | | }( |
| 40 | | σ_{b.created_at ≥ CURRENT_DATE - 1 year}( |
| 41 | | (((components c ⋈_{c.id=bc.component_id} build_component bc) |
| 42 | | ⋈_{bc.build_id=b.id} build b) |
| 43 | | ⋈_{b.id=rb.build_id} rating_build rb) |
| 44 | | ) |
| 45 | | ) |
| 46 | | ) |
| 47 | | ) |
| 48 | | ) |
| 49 | | }}} |
| 50 | | |
| 51 | | === User Reputation Leaderboard === |
| 52 | | |
| 53 | | Report on the most valuable forgers on PCForge. The score is calculated using the following metrics: |
| 54 | | * Productivity: Number of approved builds created (Weight: 10). |
| 55 | | * Popularity: Number of times their builds were favourited (Weight: 5). |
| 56 | | * Quality: The average star rating across all their builds (Weight: 20). |
| 57 | | |
| 58 | | {{{ |
| 59 | | WITH build_stats AS ( |
| 60 | | SELECT |
| 61 | | b.id AS build_id, |
| 62 | | b.user_id, |
| 63 | | COUNT(DISTINCT fb.user_id) AS favorites_count, |
| 64 | | AVG(rb.value) AS avg_rating |
| 65 | | FROM build b |
| 66 | | LEFT JOIN favorite_build fb ON b.id = fb.build_id |
| 67 | | LEFT JOIN rating_build rb ON b.id = rb.build_id |
| 68 | | WHERE b.is_approved = TRUE |
| 69 | | GROUP BY b.id, b.user_id |
| 70 | | ), |
| 71 | | user_stats AS ( |
| 72 | | SELECT |
| 73 | | user_id, |
| 74 | | COUNT(build_id) AS approved_builds_count, |
| 75 | | SUM(favorites_count) AS total_favorites_received, |
| 76 | | AVG(avg_rating) AS avg_rating_received |
| 77 | | FROM build_stats |
| 78 | | GROUP BY user_id |
| 79 | | ) |
| 80 | | SELECT |
| 81 | | u.username, |
| 82 | | u.email, |
| 83 | | us.approved_builds_count, |
| 84 | | us.total_favorites_received, |
| 85 | | ROUND(us.avg_rating_received, 2) AS avg_rating_received, |
| 86 | | ( |
| 87 | | (us.approved_builds_count * 10) + |
| 88 | | (us.total_favorites_received * 5) + |
| 89 | | (us.avg_rating_received * 20) |
| 90 | | ) AS reputation_score |
| 91 | | FROM user_stats us |
| 92 | | JOIN users u ON u.id = us.user_id |
| 93 | | ORDER BY reputation_score DESC |
| 94 | | LIMIT 10; |
| 95 | | }}} |
| 96 | | |
| 97 | | {{{ |
| 98 | | build_stats = |
| 99 | | γ_{b.id → build_id, b.user_id; |
| 100 | | COUNT_DISTINCT(fb.user_id) → favorites_count, |
| 101 | | AVG(rb.value) → avg_rating |
| 102 | | } ( |
| 103 | | σ_{b.is_approved = TRUE} ( |
| 104 | | (build b |
| 105 | | ⟕_{b.id = fb.build_id} favorite_build fb) |
| 106 | | ⟕_{b.id = rb.build_id} rating_build rb |
| 107 | | ) |
| 108 | | ) |
| 109 | | |
| 110 | | user_stats = |
| 111 | | γ_{user_id; |
| 112 | | COUNT(build_id) → approved_builds_count, |
| 113 | | SUM(favorites_count) → total_favorites_received, |
| 114 | | AVG(avg_rating) → avg_rating_received |
| 115 | | } ( |
| 116 | | build_stats |
| 117 | | ) |
| 118 | | |
| 119 | | λ_10 ( |
| 120 | | τ_{reputation_score DESC} ( |
| 121 | | π_{u.username, |
| 122 | | u.email, |
| 123 | | us.approved_builds_count, |
| 124 | | us.total_favorites_received, |
| 125 | | ROUND(us.avg_rating_received, 2) → avg_rating_received, |
| 126 | | ((us.approved_builds_count * 10) |
| 127 | | + (us.total_favorites_received * 5) |
| 128 | | + (us.avg_rating_received * 20)) → reputation_score |
| 129 | | } ( |
| 130 | | user_stats us ⋈_{u.id = us.user_id} users u |
| 131 | | ) |
| 132 | | ) |
| 133 | | ) |
| 134 | | }}} |
