Please note that some of these tips might not be relevant for all RDBMs. For example, the :: syntax (tip 5) does not work in SQLite.
Anti-joins are incredible useful, mostly (in my experience) for when when you only want to return rows/values from one table that aren't present in another table.
QUALIFY lets you filter the results of a query based on a window function. This is useful for a variety of reasons, including to reduce the number of lines of code needed.
NOT IN doesn't work if NULL is present in the values being checked against. As NULL represents Unknown the SQL engine can't verify that the value being checked is not present in the list.
When creating a calculated field you might be tempted to rename it to an existing column but this can lead to unexpected behaviour, such as a window function operating on the wrong field:
Additionally, your RDBMS might raise an error if two tables share the same column name and you don't specify which column you are using.