Please help build Query

[Yorisoft]

First thanks for making this publicaly available.

TLDR:
Please help me create an SQLite query that retrieves the in game locations + encounter rate like it does on the veekun website.

I'm trying to make my own pokedex using the .cvs files. I was hoping you could help me figure out a specific query to get specific data.

I'd like to get the encounter information for a pokemon for a given game/version.
So far this is what I've got. Which is pretty close.
version_id: 1
pokemon_id: 264

---------------------------------------------
version	route_or_location	encounter_method	min_level	max_level	total_encounter_rate	total_slot_number	location_area_name	encounter_conditions
ruby	Route 118	walk	26	26	**114**	1		
ruby	Route 119	walk	25	27	**218**	2		
ruby	Route 120	walk	25	27	**228**	2		
ruby	Route 121	walk	26	28	**228**	2		
ruby	Route 123	walk	26	28	**228**	2		

The only issue is that my query doesn't work past generation 2 games. For some reason the calculations for gen 3 and onward are incorrect and many go past the 100% mark. I've tried asking ChatGPT but could only get so far. Hoping I could get some help here.

Here is the query I'm currently using, but keep in mind would be completely willing to use a brand new one as long as it provides similar data/columns. Using SQLite

SELECT 
    v.identifier AS version,
    ln.name AS route_or_location,
    em.identifier AS encounter_method,
    MIN(e.min_level) AS min_level,
    MAX(e.max_level) AS max_level,
    SUM(COALESCE(laer.rate, es.rarity, 0)) AS total_encounter_rate,
    COUNT(DISTINCT e.encounter_slot_id) AS total_slot_number,
    la.identifier AS location_area_name,
    ecv.identifier AS encounter_conditions
FROM 
    encounters e
JOIN 
    location_areas la ON e.location_area_id = la.id
JOIN 
    locations l ON la.location_id = l.id
JOIN 
    versions v ON e.version_id = v.id
LEFT JOIN 
    location_area_encounter_rates laer ON laer.location_area_id = la.id AND laer.version_id = v.id
LEFT JOIN 
    encounter_slots es ON e.encounter_slot_id = es.id
LEFT JOIN 
    encounter_methods em ON es.encounter_method_id = em.id
LEFT JOIN 
    location_names ln ON l.id = ln.location_id AND ln.local_language_id = 9 
LEFT JOIN 
    encounter_condition_value_map ecm ON ecm.encounter_id = e.id
LEFT JOIN 
    encounter_condition_values ecv ON ecm.encounter_condition_value_id = ecv.id
WHERE 
    e.pokemon_id = :pokemon_id
    AND v.id = :version_id
GROUP BY 
    v.identifier, ln.name, em.identifier, la.identifier, ecv.identifier 
ORDER BY 
    l.id, v.identifier, ln.name, em.identifier, ecv.identifier;

GEN 1 EXAMPLE

GEN 2 EXAMPLE

GEN 3 EXAMPLE

A bit more info here Yorisoft/pokedex_miyoo#7