PostgreSQL and Querying Pokémon

Install (Lessons Learned)

I installed PostgreSQL directly from the website here. I also immediately downloaded the corresponding documentation from here. After installing in my local ‘C:\Program Files’ folder, I ran into an odd issue where I could not log into the default user in psql, which also happened to not be the postgre default user. After opening pgAdmin4 and reading the logs found in data/logs/, the solution was to add both a Database and Login/Group Role with the same name as the user defaulting in. This allowed me to finally ‘log in’ to psql.

Overall, I found the overall query writing experience to be better using pgAdmin directly, than trying to write in psql command line. I have used MS SQL in the past, so the Query Tool within pgAdmin was very familiar. It is also useful see the tables and views directly in your database.

Using Window Function to Find Pokémon First Type Ranking Based on Total Base Points

In order to accommodate multiple typing, I decided to do do two average window functions over the two types and then average the two results. Although a perfect measurement, averaging the total_points of the two types and then taking the average of that number still gives insight into how powerful a Pokémon is comparatively to other Pokémon with the same type 1 and type 2 typing. The results were then sorted by the first Pokémon type.

WITH pkmn_win as (SELECT pokedex_number, name, type_1, type_2, total_points,
AVG(total_points) OVER(PARTITION BY type_1) as avg_type1,
AVG(total_points) OVER(PARTITION BY type_2) as avg_type2,
rank() OVER (PARTITION BY type_1 ORDER BY total_points DESC) type_rnk
FROM pkmn
ORDER BY pokedex_number)

SELECT 
pokedex_number, name, type_1, type_2, total_points,
(avg_type1 + avg_type2)/2 as avg_avg,
rank() OVER (PARTITION BY ((avg_type1 + avg_type2)/2) ORDER BY total_points DESC) avg_rnk
FROM pkmn_win

Top 15 Results:

Finding Total Count of Pokémon with a Unique Type Combination

There are 18 Pokémon types; however, the second typing (type 2) can technically have 19 unique value, due to no secondary typing requirements. For this reason, types were concatenated in their ‘forward’ position (type 1, type 2) and their ‘backwards’ position (type 2, type 1), and then counted in separate CTEs. The results were joined on the backwards concatenation to ensure all type combinations were present. The separate count columns were then summed to get the overall count.

with bkwd as (
select
count(pokedex_number) count_bkw,
concat(type_2,',',type_1) as bkw
from pkmn
group by bkw
),

fwrd as (select 
count(pokedex_number) count_fwd,
concat(type_1,',',type_2) as fwd
from pkmn 
group by fwd
)

select 
bkwd.bkw as grp,
bkwd.count_bkw as count_bkw,
(case when(fwrd.count_fwd is null) then 0 else fwrd.count_fwd end) as count_fwd,
(bkwd.count_bkw + (case when(fwrd.count_fwd is null) then 0 else fwrd.count_fwd end)) as ttl
from bkwd 
left outer join fwrd on fwrd.fwd = bkwd.bkw 
order by ttl desc

Top 15 Results:

Returning List of Pokémon with a Unique Type Combination

To select the Pokémon with the most common typing, the previous query was put into another CTE and filtered in a WHERE clause against the complete data.

with bkwd as (
select
count(pokedex_number) count_bkw,
concat(type_2,',',type_1) as bkw
from pkmn
group by bkw
),

fwrd as (select 
count(pokedex_number) count_fwd,
concat(type_1,',',type_2) as fwd
from pkmn 
group by fwd
),

rnk as (select
bkwd.bkw as grp,
bkwd.count_bkw as count_bkw,
fwrd.count_fwd as count_fwd,
(bkwd.count_bkw + (case when(fwrd.count_fwd is null) then 0 else fwrd.count_fwd end)) as ttl,
rank () OVER(order by (bkwd.count_bkw + (case when(fwrd.count_fwd is null) then 0 else fwrd.count_fwd end)) desc) as rnk
from bkwd 
left outer join fwrd on fwrd.fwd = bkwd.bkw 
order by ttl desc)

select *,
rank () OVER(ORDER BY total_points desc) type1_rank
from pkmn 
where concat(type_2,',',type_1) = (select rnk.grp from rnk where rnk = 1)

Top 15 Results: