sandyGanoti

-- update survived scoreGroup for non official leaderboards, set user_ids equals to the comma-separated-winner-users

update score_group set user_ids=(select substring_index(GROUP_CONCAT(DISTINCT pickem.entry.user_id SEPARATOR ','), ',', 30) as expecting_ids from user_leaderboard left join pickem.entry on pickem.entry.user_id=user_leaderboard.user_id where pickem.entry.winner=1 and pickem.entry.contest_id=1 and leaderboard_id=score_group.leaderboard_id) where leaderboard_id in (select id from leaderboard where official=0) and name="Survived";


-- update eliminated scoreGroup for non official leaderboards, set user_ids equals to the comma-separated-eliminated-users

update score_group set user_ids=(select substring_index(GROUP_CONCAT(DISTINCT pickem.entry.user_id SEPARATOR ','), ',', 30) as expecting_ids from user_leaderboard left join pickem.entry on pickem.entry.user_id=user_leaderboard.user_id where pickem.entry.winner=0 and pickem.entry.contest_id=1 and leaderboard_id=score_group.leaderboard_id) where lea

sandyGanoti

-- 1. The following query populates the user_ids with a comma-separated value of some user_ids (some is specified inside the substring_index function) on survived score_group

update score_group set user_ids=(select substring_index(GROUP_CONCAT(user_id SEPARATOR ','), ',', 3) as expecting_ids from user_leaderboard where leaderboard_id=score_group.leaderboard_id  and name='Survived') where leaderboard_id in (select id from leaderboard);

----------------
-- 2. The following query updates the survived score groups with the number of users that belong to its leaderboard

update score_group set no_of_users=(select count(*) from user_leaderboard where leaderboard_id=score_group.leaderboard_id) where leaderboard_id in (select id from leaderboard) and name='Survived';

sandyGanoti

insert ignore into scoregroups.user_leaderboard(user_id, leaderboard_id) select distinct(pickem_entry.user_id), "<leaderboard_id_HERE>" from pickem.entry as pickem_entry where pickem_entry.contest_id=<contest_id_HERE>;