Changes between Version 1 and Version 2 of BuildPopularity

Timestamp:

01/29/26 03:50:53 (10 days ago)

Author:

233051

Comment:

--

BuildPopularity

@@ -8 +8 @@
 
 {{{
+CREATE OR REPLACE FUNCTION get_report_budget_tier_popularity()
+    RETURNS TABLE (
+                      price_tier TEXT,
+                      builds_count BIGINT,
+                      avg_favorites NUMERIC,
+                      avg_rating NUMERIC,
+                      unique_builders BIGINT,
+                      engagement_score NUMERIC
+                  )
+    LANGUAGE sql
+AS $$
 WITH price_tier_builds AS (
-  SELECT
-    b.id AS build_id,
-    b.user_id,
-    b.total_price,
-    CASE 
-      WHEN b.total_price < 500 THEN 'Budget'
-      WHEN b.total_price < 1000 THEN 'Mid-Range'
-      WHEN b.total_price < 2000 THEN 'High-End'
-      ELSE 'Enthusiast'
-    END AS price_tier
-  FROM build b
-  WHERE b.is_approved = TRUE
-    AND b.created_at >= CURRENT_DATE - INTERVAL '6 months'
+    SELECT
+        b.id AS build_id,
+        b.user_id,
+        b.total_price,
+        CASE
+            WHEN b.total_price < 500 THEN 'Budget'
+            WHEN b.total_price < 1000 THEN 'Mid-Range'
+            WHEN b.total_price < 2000 THEN 'High-End'
+            ELSE 'Enthusiast'
+            END AS price_tier
+    FROM build b
+    WHERE b.is_approved = TRUE
+      AND b.created_at >= CURRENT_DATE - INTERVAL '6 months'
 ),
-engagement_stats AS (
-  SELECT
-    ptb.price_tier,
-    COUNT(ptb.build_id) AS builds_count,
-    AVG(fb.favorites_count) AS avg_favorites,
-    AVG(rb.value) AS avg_rating,
-    COUNT(DISTINCT ptb.user_id) AS unique_builders
-  FROM price_tier_builds ptb
-  JOIN rating_build rb ON ptb.build_id = rb.build_id
-  JOIN (
-    SELECT build_id, COUNT(DISTINCT user_id) AS favorites_count
-    FROM favorite_build
-    GROUP BY build_id
-  ) fb ON ptb.build_id = fb.build_id
-  GROUP BY ptb.price_tier
-)
+     favorites AS (
+         SELECT
+             build_id,
+             COUNT(DISTINCT user_id) AS favorites_count
+         FROM favorite_build
+         GROUP BY build_id
+     ),
+     engagement_stats AS (
+         SELECT
+             ptb.price_tier,
+             COUNT(DISTINCT ptb.build_id) AS builds_count,
+             AVG(COALESCE(f.favorites_count, 0)) AS avg_favorites,
+             AVG(rb.value) AS avg_rating,
+             COUNT(DISTINCT ptb.user_id) AS unique_builders
+         FROM price_tier_builds ptb
+                  LEFT JOIN rating_build rb ON ptb.build_id = rb.build_id
+                  LEFT JOIN favorites f ON ptb.build_id = f.build_id
+         GROUP BY ptb.price_tier
+     )
 SELECT
-  price_tier,
-  builds_count,
-  ROUND(avg_favorites, 1) AS avg_favorites,
-  ROUND(avg_rating, 2) AS avg_rating,
-  unique_builders,
-  ROUND(
-    (builds_count * 2) + 
-    (avg_favorites * 3) + 
-    (avg_rating * 10) + 
-    (unique_builders * 1.5), 2
-  ) AS engagement_score
+    price_tier,
+    builds_count,
+    ROUND(CAST(avg_favorites AS numeric), 1) AS avg_favorites,
+    ROUND(CAST(COALESCE(avg_rating, 0) AS numeric), 2) AS avg_rating,
+    unique_builders,
+    ROUND(
+            CAST(
+                    (builds_count * 2) +
+                    (avg_favorites * 3) +
+                    (COALESCE(avg_rating, 0) * 10) +
+                    (unique_builders * 1.5)
+                AS numeric),
+            2
+    ) AS engagement_score
 FROM engagement_stats
 ORDER BY engagement_score DESC
 LIMIT 15;
+$$;
 }}}