I've generally started to treat use of SELECT DISTINCT as a warning flag, as it's very common that it indicates bad code

Some use it because they don't understand uniqueness constraints and try to fix it in post so to say. Some use it because they forgot a join condition and are absolute amateurs. Some use it because it fixed a problem for them once and now they add it everywhere

These people seem to outnumber the people who use SELECT DISTINCT in a well thought out manner