This page is about using SQL patterns in real analytical work—not memorizing interview questions. The focus is query logic, validation, and business context.
1. Filter deliberately
Use WHERE to define the population. Be explicit about dates, statuses, nulls, and exclusions.
2. Aggregate at the right grain
GROUP BY changes the grain of your dataset. State what one output row represents before trusting the metric.
3. Use CASE for business rules
CASE expressions are useful for flags and categories, but important rules should be named and documented.
4. Treat JOINs as row-set operations
Know the expected relationship between keys. Check row counts and distinct keys before and after the join.
5. Use CTEs to separate logic
Break complex analysis into understandable stages so each step can be inspected independently.
6. Use window functions
ROW_NUMBER, RANK, LAG, LEAD, and running aggregates let you compare records without collapsing the underlying rows.
7. Deduplicate intentionally
Define the business key and what ‘latest’ means before using ROW_NUMBER to select one record.
8. Conditional aggregation
SUM and COUNT with conditions can calculate multiple consistent metrics from the same population.
9. Be careful with dates
Time zones, timestamps, fiscal periods, and inclusive boundaries can change results. Validate date assumptions.
10. Handle NULL explicitly
NULL is not zero. Decide whether missingness means unknown, not applicable, or no activity.
11. Write reconciliation queries
Compare counts, sums, distinct keys, and unmatched records across sources. Small validation queries can prevent large reporting errors.
12. Explain the query in plain English
If you cannot explain every stage, you cannot reliably review an AI-generated query. SQL fluency is analytical judgment, not syntax memorization.