You may want to format your DDL/DQL and add some white space. Presently, it's not legible without actually doing this work in my opinion. pgFormatter or similar tools would help.
Presently, this is just one big block of statements with comments mixed in, but you are essentially asking for community code review as I see it. So, in this spirit, I strongly urge you to improve the code legibility to help others help you.
Asking (or expecting) the community to copy code and reformat, or build tables and populate, just to try to find an issue with your left join is likely unnecessary.
Likely, there is an apparent answer. The level of effort to quickly respond meaningfully is presently high though (at least for me).