The summary.
UNDERSTAND THE EXECUTION ORDER
Join, filter, group, filter groups, compute, sort, limit. It explains most confusing errors and why filtering early is faster.
THE COMMONEST JOIN ERROR
A filter on the right table of a left join, in the where clause — it silently becomes an inner join. Put the condition in the join.
And compare row counts before and after every join. Fan-out produces inflated totals that look plausible and are wrong.
USE NOT-EXISTS RATHER THAN NOT-IN
A not-in condition returns nothing at all when the list contains a null.
NUMBERING ROWS WITHIN A GROUP AND KEEPING THE FIRST IS THE MOST USEFUL PATTERN IN THE DISCIPLINE
NEVER USE BETWEEN FOR TIMESTAMP RANGES
It includes the endpoint, which excludes most of the final day. Use greater-or-equal to the start and less-than the next start.
Store one time zone and convert at query time.
VERIFY EVERY RESULT AGAINST A FIGURE YOU ALREADY KNOW
It catches fan-out, wrong filters and wrong grain immediately.
DO NOT ASSUME CONSTRAINTS EXIST IN A WAREHOUSE
Many analytical engines do not enforce them, so uniqueness must be tested rather than trusted.
