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:

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'
),
     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(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;
$$;

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