WITH Base AS (
                    SELECT
                        p.playerId,
                        p.last_event,
                        p.connection_time,
                        p.lastName,
                        p.flag,
                        p.country,
                        p.kills,
                        p.deaths,
                        p.skill,
                        p.shots,
                        p.hits,
                        p.headshots,
                        p.last_skill_change,
                        p.kill_streak,
                        p.death_streak,
                        p.activity,
                        uid.uniqueId
                    FROM hlstats_players p
                    LEFT JOIN hlstats_PlayerUniqueIds uid ON uid.playerId = p.playerId
                    WHERE p.hideranking = 0
                    AND p.lastAddress <> ''
                    AND p.game = 'hl2mp'
                    AND p.flag = 'US'
                ),
                Ranked AS (
                    SELECT
                        *,
                        RANK() OVER (ORDER BY skill DESC, kills DESC) AS rank_position,
                        COUNT(*) OVER() AS total_rows
                    FROM Base
                )
                SELECT *,
                    ROUND(IF(deaths=0, 0, kills/deaths), 2) AS kpd,
                    ROUND(IF(kills=0, 0, headshots/kills), 2) AS hpk,
                    ROUND(IF(shots=0, 0, hits/shots), 3) AS acc
                FROM Ranked
                ORDER BY rank_position ASC,
                        skill DESC,
                        connection_time DESC
                LIMIT 20 OFFSET 0;