Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Building a Better Pokédex with SQL

Building a Better Pokédex with SQL
July 13, 2016

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.html.name,
  attack_multiplier
from
  type_multipliers
  join move_type on
    move_type._type = attacking_type
  join original_pokemon.html on
    original_pokemon.html.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.html on
   original_pokemon.html.type_1 = defending_type
   OR original_pokemon.html.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.html with second types, and to check my work again I issued a quick query for

select count(*) from original_pokemon.html 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.html.name,
  EXP(sum(ln(attack_multiplier)))
from
  pokemon.html_type_multipliers
  join move_type on
    move_type._type = attacking_type
  join original_pokemon.html on
    original_pokemon.html.type_1 = defending_type
    or original_pokemon.html.type_2 = defending_type
group by
  move_type.name
  , original_pokemon.html.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.html 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.html.type_1 = move_type._type
    or attacking_pokemon.html.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.html_name as pokemon.html
      , move_name as move
      , original_pokemon.html.type_1 as type_1
      , original_pokemon.html.type_2 as type_2
      , pokemon.html_move_types._type as move_type
    from
      pokemon.html_moves
      join pokemon.html_move_types on
        move_name = name
      join original_pokemon.html on
        original_pokemon.html.name = pokemon.html_name
    where
      pokemon.html_name=attacking_pokemon.html and
      move_name=move
  )
  , defending_pokemon.html as (
    select
      name
      , type_1
      , type_2
    from
      original_pokemon.html
    where
      name=defending_pokemon.html
  )
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.html_type_multipliers
  join attack on
    attack.move_type = attacking_type
  join defending_pokemon.html on
    defending_pokemon.html.type_1 = defending_type
    or defending_pokemon.html.type_2 = defending_type
group by
  attack.pokemon.html
  , attack.move_type
  , attack.move
  , attack.type_1
  , attack.type_2
  , defending_pokemon.html.name

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

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?