Changes between Initial Version and Version 1 of BuildPopularity

Timestamp:

01/28/26 00:33:06 (12 days ago)

Author:

233051

Comment:

--

BuildPopularity

@@ +1 @@
+= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
+
+===  Budget-Tier Build Popularity Analysis ===
+
+Report on which price ranges have the highest user engagement, which measures both build creation volume and the average community reception. 
+
+**SQL:**
+
+{{{
+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'
+),
+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
+)
+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
+FROM engagement_stats
+ORDER BY engagement_score DESC
+LIMIT 15;
+}}}
+
+**Relational algebra:**
+
+{{{
+price_tier_builds = 
+  π_{b.id ⇒ build_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 ⇒ price_tier
+  } (
+    σ_{b.is_approved = TRUE AND b.created_at ≥ CURRENT_DATE - INTERVAL '6 months'} (build b)
+  )
+
+engagement_stats = 
+  γ_{price_tier; 
+     COUNT(build_id) ⇒ builds_count,
+     AVG(favorites_count) ⇒ avg_favorites,
+     AVG(rb.value) ⇒ avg_rating,
+     COUNT(DISTINCT user_id) ⇒ unique_builders
+  } (
+    price_tier_builds ⋈_{price_tier_builds.build_id = rb.build_id} rating_build rb
+    ⋈_{rb.build_id = b.id} build b
+  )
+
+λ_15 (
+  τ_{(builds_count * 2) + (avg_favorites * 3) + (avg_rating * 10) + (unique_builders * 1.5) DESC} (
+    π_{e.price_tier,
+       e.builds_count,
+       e.avg_favorites,
+       ROUND(e.avg_rating, 2) ⇒ avg_rating,
+       e.unique_builders,
+       (e.builds_count * 2 + e.avg_favorites * 3 + e.avg_rating * 10 + e.unique_builders * 1.5) ⇒ engagement_score
+    } (engagement_stats e)
+  )
+)
+}}}