Step 1: UPDATE sightings so that reporter and codemon lose their surrounding spaces and get a capital first letter followed by lowercase letters — ' alex' becomes 'Alex', 'PLANTACHU' becomes 'Plantachu' (TRIM, UPPER, LOWER, SUBSTR and ||).
Step 2: add an INTEGER column level with ALTER TABLE, then fill it with UPDATE: level_text converted with CAST, or NULL when level_text is '' or 'unknown'.
Step 3: DELETE the sightings whose codemon is not a name of the codemon table — the names are clean now, so a plain NOT IN subquery does the job.
Step 4: the report, one row per Codémon: codemon, its type (join the codemon table), sightings (how many), avg_weight (average weight_kg rounded to 2 decimals) and best_level (the highest level, NULL when every level is unknown), most sighted first, then by codemon — 8 rows.