## PART 4: FINAL REPORT

# Sort the data by finding out which trainer has the strongest pokemon so that this will act as a ranking of strongest to weakest trainer. You may interpret strongest in whatever way you want, but you will have to explain your decision.
# The "strongest" Pokemon are determined by first sorting according to level, then within each level "group" they are sorted according to average stats (only maxhp was accounted for in the calculation because presumably hp just refers to the Pokemons current hp in battle), then they were sorted alphabetically because some Pokemon are pretty evenly matched in terms of level and stats so the database was returning ugly intersected results
SELECT pokemons.name as "Species", trainers.trainername as "TrainerName", p_t.pokelevel as "Level", ANY_VALUE(prime.name) as "PrimeType", ANY_VALUE(second.name) as "SecondType"
FROM pokemon_trainer p_t
JOIN trainers ON (trainers.trainerID = p_t.trainerID)
JOIN pokemons ON (pokemons.id = pokemon_id)
JOIN types prime ON (prime.id = pokemons.primary_type)
JOIN types second ON (second.id = pokemons.secondary_type)
GROUP BY pokemons.name, trainers.trainerID, pokelevel
ORDER BY pokelevel DESC, (SUM(p_t.maxhp + p_t.attack + p_t.defense + p_t.spatk + p_t.spdef + p_t.speed) / 6) DESC, pokemons.name DESC;

# This table is comprised of the truly strongest of the strong, aka only Dedennes, because Dedennnes are so small and chubby and good
SELECT pokemons.name as "Species", trainers.trainername as "TrainerName", pokemon_trainer.pokelevel as "Level", ANY_VALUE(prime.name) as "PrimeType", ANY_VALUE(second.name) as "SecondType"
FROM pokemon_trainer
JOIN trainers ON (trainers.trainerID = pokemon_trainer.trainerID)
JOIN pokemons ON (pokemons.id = pokemon_id)
JOIN types prime ON (prime.id = pokemons.primary_type)
JOIN types second ON (second.id = pokemons.secondary_type)
WHERE pokemons.name = "Dedenne"
GROUP BY pokemons.name, trainers.trainerID, pokelevel
ORDER BY pokelevel DESC;