No items found.

# Building a Better Pokédex with SQL

Pokémon Go has taken over the world. In the moments I’m not exploring the city trying to catch ‘em all, I’ve found myself idly wondering about Pokémon Theory. What moves are super effective against which types again? What is a critical hit? And how did those multipliers actually work anyway?

With the quest to calculate the attack multiplier for any and every potential matchup between two pokémon, I set off to explore the internet and build the Pokémon Battle Simulator

## Collecting Data

First order of business, I needed to figure out how attack multipliers actually work. Thanks to Bulbapedia, I learned that it is a matchup of the attacking move’s type, and the type of the defending pokémon, and with the help of the PokeAPI, I was certain I could create a tool allowing a matchup between any two pokémon and any move the attacking pokémon had learned.

I built up a table of pokémon, and joined on their types:

Select
move_type.name,
original_pokemon.name,
attack_multiplier
from
type_multipliers
join move_type on
move_type._type = attacking_type
join original_pokemon on
original_pokemon.type_1 = defending_type

The resulting table should give one row for every combination of move and pokémon. The PokeAPI returned 620 moves, and I grabbed the original 151 Pokémon, so to check my work I verified that the returned row count of the query was 151 x 620 = 93,260. There are a lot of matchup charts available, but I wanted to start adding in the subtleties of damage calculation. The first expansion was to calculate the damage multiplier when the defending pokémon has two types. We need to look up the relevant multipliers for each of the defending pokémon’s types and multiply them together.

To get all relevant entries for a defending pokémon, I added an OR clause in the join statement to match on type_1 as well as type_2:

join original_pokemon on
original_pokemon.type_1 = defending_type
OR original_pokemon.type_2 = defending_type

The table now has 135,160 rows, which I used to calculate the number of Pokémon that have two types: 620 types x (151 pokémon’s first types + X pokémon’s second types) = 135,160. Solving this gives 67 out of 151 original pokemon with second types, and to check my work again I issued a quick query for

select count(*) from original_pokemon where type_2 != ''

We now have a solid pokédex to perform our analysis.

## Building the Battle Simulator

Next I needed to aggregate back down to a single row for every combination of attack move and defending pokémon. When I hit this step I realized all the basic aggregating functions I knew off the top of my head like sum, count, avg, max, etc. wouldn’t cut it! I wanted to group by attack move and defending pokémon, but I needed an aggregate to multiply all the grouped values together. After some digging, I found a useful workaround for a product aggregate function in SQL, which is to sum the logs of the values, then take the exponent of the resulting sum:

EXP(sum(ln(attack_multiplier)))

Along with an added group by clause, the final query for a table of all moves against all pokémon looked like:

select
move_type.name,
original_pokemon.name,
EXP(sum(ln(attack_multiplier)))
from
pokemon_type_multipliers
join move_type on
move_type._type = attacking_type
join original_pokemon on
original_pokemon.type_1 = defending_type
or original_pokemon.type_2 = defending_type
group by
move_type.name
, original_pokemon.name

With some Periscope magic, I turned this table into a beautiful color-coded cohort table:

The last level of subtlety I wanted to be able to calculate is commonly known as STAB (same type attack bonus). In this case, when the attacking pokémon is the same type as the attack move, the total damage is multiplied by 150%. Of course, the attacking pokemon might have as many as two types, and if either matches the type of the move, the 1.5 multiplier takes effect. For this a case statement sufficed:

case
when attacking_pokemon.type_1 = move_type._type
or attacking_pokemon.type_2 = move_type._type
then EXP(sum(ln(attack_multiplier))) * 1.5
else EXP(sum(ln(attack_multiplier)))
end

The final query calculates the multiplier for any move by any attacking pokémon against any defending pokémon:

with
attack as (
select
pokemon_name as pokemon
, move_name as move
, original_pokemon.type_1 as type_1
, original_pokemon.type_2 as type_2
, pokemon_move_types._type as move_type
from
pokemon_moves
join pokemon_move_types on
move_name = name
join original_pokemon on
original_pokemon.name = pokemon_name
where
pokemon_name=attacking_pokemon and
move_name=move
)
, defending_pokemon as (
select
name
, type_1
, type_2
from
original_pokemon
where
name=defending_pokemon
)
select
case
when attack.type_1 = attack.move_type
or attack.type_2 = attack.move_type
then EXP(sum(ln(attack_multiplier))) * 1.5
else EXP(sum(ln(attack_multiplier)))
end
from
pokemon_type_multipliers
join attack on
attack.move_type = attacking_type
join defending_pokemon on
defending_pokemon.type_1 = defending_type
or defending_pokemon.type_2 = defending_type
group by
attack.pokemon
, attack.move_type
, attack.move
, attack.type_1
, attack.type_2
, defending_pokemon.name

And there we have it! Checkout the interactive dashboard for analyzing Pokémon Battle Matchups.