USE betpa_game;

DROP VIEW IF EXISTS leaderboard_world;
DROP VIEW IF EXISTS leaderboard_country;
DROP VIEW IF EXISTS leaderboard_city;

CREATE VIEW leaderboard_world AS
SELECT id, username, city, country, wins, total_points,
       DENSE_RANK() OVER (ORDER BY total_points DESC, wins DESC, created_at ASC) AS rank_no
FROM users;

CREATE VIEW leaderboard_country AS
SELECT id, username, city, country, wins, total_points,
       DENSE_RANK() OVER (PARTITION BY country ORDER BY total_points DESC, wins DESC, created_at ASC) AS rank_no
FROM users;

CREATE VIEW leaderboard_city AS
SELECT id, username, city, country, wins, total_points,
       DENSE_RANK() OVER (PARTITION BY country, city ORDER BY total_points DESC, wins DESC, created_at ASC) AS rank_no
FROM users;