BEGIN
WITH ranked_players AS (
SELECT
player_id,
DENSE_RANK() OVER (
ORDER BY average_cpm DESC, average_accuracy DESC
) as new_rank
FROM
public.player_stats
)
UPDATE public.player_stats ps
SET rank = rp.new_rank
FROM ranked_players rp
WHERE ps.player_id = rp.player_id
AND (ps.rank IS NULL OR ps.rank != rp.new_rank);
END;
BEGIN
WITH ranked_players AS (
SELECT
player_id,
DENSE_RANK() OVER (
ORDER BY average_cpm DESC, average_accuracy DESC
) as new_rank
FROM
public.player_stats
)
UPDATE public.player_stats ps
SET rank = rp.new_rank
FROM ranked_players rp
WHERE ps.player_id = rp.player_id
AND (ps.rank IS NULL OR ps.rank != rp.new_rank);
END;