[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...

[andersmurphy]

Non sargability easy to solve with expression indexes. At least in sqlite.