Step 1: a CTE badge_counts with, for each trainer_id of the badges table, the number of badges as badges.
Step 2: a CTE win_counts with, for each winner_id of the battles table, wins (battles won) and home_wins (battles won in a gym whose city is the winner's hometown). Join gyms and trainers, and count the home wins with SUM(CASE … END).
Step 3: a CTE last_events with, for each trainer, last_event: the most recent date among their badges (earned_on) and the battles they won (battle_date). Stack the two lists of dates with UNION ALL, then take the MAX per trainer.
Step 4: the final query. Every trainer (Jordan included) with name, hometown, badges, wins, home_wins (0 when none) and last_event (NULL when none): trainers joined to the three CTEs with LEFT JOIN, sorted by wins descending, then badges descending, then name.