analyze_player_rating_trend.sqlā¢586 B
-- Player rating trends analysis
-- analyze_player_rating_trend: Analyze rating trends for a player
SELECT
game_type,
MIN(rating) as min_rating,
MAX(rating) as max_rating,
AVG(rating) as avg_rating,
COUNT(*) as data_points,
MAX(recorded_at) - MIN(recorded_at) as time_span_days,
-- Calculate trend using linear regression approximation
CORR(EXTRACT(EPOCH FROM recorded_at), rating) as trend_coefficient
FROM rating_history
WHERE username = $username
AND recorded_at >= CURRENT_DATE - INTERVAL ($days || ' days')
GROUP BY game_type
ORDER BY game_type;