Best practices for writing SQL queries:

1- Filter Early, Aggregate Late: Apply filtering conditions in the WHERE clause early in the query, and perform aggregations in the HAVING or SELECT clauses as needed.

2- Use table aliases with columns when you are joining multiple tables.

3- Never use select *, always mention list of columns in select clause before deploying the code.

4- Add useful comments wherever you write complex logic. Avoid too many comments.

5- Use joins instead of correlated subqueries when possible for better performance.

6- Create CTEs instead of multiple sub queries, it will make your query easy to read.

7- Join tables using JOIN keywords instead of writing join condition in where clause for better readability.

8- Never use order by in sub queries, It will unnecessary increase runtime. In fact some databases don't even allow you to do that.

9- If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.