Comment by thehours
> Mishandling Excessive Case When Statements
User Defined Functions (UDFs) are another option to consolidate the logic in one place.
> Using Functions on Indexed Columns
In other words, the query is not sargable [0]
> Overusing DISTINCT to “Fix” Duplicates
Orthogonal to author's point about dealing with fanout from joins, I'm a fan of using something like this for 'de-duping' records that aren't exact matches in order to conform the output to the table grain:
ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1
Some database engines have QUALIFY [1], which lends itself to a fairly clean query.[0] https://en.wikipedia.org/wiki/Sargable
[1] https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_cla...
Non sargability easy to solve with expression indexes. At least in sqlite.