I am a bit biased here in terms of resources, I worked with a few people to create a free resource that covers intro to sql optimization: https://dataschool.com/sql-optimization
I also created a bunch of animations to help people build mental models for joins, aggregations, window functions, and subqueries which I find is the main blocker for people using SQL optimally: https://dataschool.com/how-to-teach-people-sql/