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