analyze_game_results.sqlā¢1.67 kB
-- Analyze game results for a player
-- analyze_game_results: Analyze win/loss/draw patterns for a player
WITH game_results AS (
SELECT
game_data->>'time_class' as time_class,
CASE
WHEN game_data->'white'->>'username' = $username THEN
CASE game_data->'white'->>'result'
WHEN 'win' THEN 'win'
WHEN 'checkmated' THEN 'loss'
WHEN 'timeout' THEN 'loss'
WHEN 'resigned' THEN 'loss'
WHEN 'abandoned' THEN 'loss'
ELSE 'draw'
END
ELSE
CASE game_data->'black'->>'result'
WHEN 'win' THEN 'win'
WHEN 'checkmated' THEN 'loss'
WHEN 'timeout' THEN 'loss'
WHEN 'resigned' THEN 'loss'
WHEN 'abandoned' THEN 'loss'
ELSE 'draw'
END
END as result,
CASE
WHEN game_data->'white'->>'username' = $username THEN 'white'
ELSE 'black'
END as color
FROM chess_games_cache
WHERE username = $username
AND year >= $start_year
AND (year > $start_year OR month >= $start_month)
)
SELECT
time_class,
color,
COUNT(*) as total_games,
COUNT(CASE WHEN result = 'win' THEN 1 END) as wins,
COUNT(CASE WHEN result = 'loss' THEN 1 END) as losses,
COUNT(CASE WHEN result = 'draw' THEN 1 END) as draws,
ROUND(COUNT(CASE WHEN result = 'win' THEN 1 END) * 100.0 / COUNT(*), 2) as win_rate
FROM game_results
GROUP BY time_class, color
ORDER BY time_class, color;