Which SQL functions do you actually use?

Nobody memorizes SQL. The people who look fast simply stop re-googling the same 25 functions, because those 25 answer 80% of real queries. Here they are, one notebook page, with a five-word gloss each. The print-ready A4 PDF is at the bottom.

Aggregates

• COUNT(*): rows, nulls included.
• COUNT(DISTINCT x): uniques.
• SUM(x) / AVG(x): both skip nulls silently.
• MIN(x) / MAX(x): work on dates too.

Strings

• LOWER(x) / TRIM(x): clean before comparing, always.
• CONCAT(a,b) or a || b: glue.
• SUBSTRING(x,1,3): slice.
• REPLACE(x,'a','b'): swap.
• SPLIT_PART(x,',',1): everything before the first comma.

Dates

• DATE_TRUNC('month', d): first day of the month, the backbone of every monthly report.
• EXTRACT(year FROM d): pull one part out.
• d + INTERVAL '7 day': date math.
• CURRENT_DATE: today.

NULLs

• COALESCE(x, 0): first non-null value.
• NULLIF(x, 0): divide-by-zero armor.
• x IS NULL: never = NULL. Never.

Window functions

• ROW_NUMBER() OVER (…): the dedupe trick.
• RANK() / DENSE_RANK(): differ on ties.
• LAG(x) / LEAD(x): previous / next row.
• SUM(x) OVER (ORDER BY d): running total.

Conditionals

• CASE WHEN … THEN … END: if / else.
• COUNT(*) FILTER (WHERE …): conditional count.
• GREATEST(a,b) / LEAST(a,b): row-wise max and min.

The pattern worth stealing: dedupe

Strings and window functions together clean most real tables:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY LOWER(TRIM(email))
    ORDER BY created_at DESC) AS rn
  FROM customers
) t WHERE rn = 1;

Normalize inside the PARTITION BY, keep the newest row per person, drop the rest.

The trap: NULL breaks your math

NULL is not a value, it is "unknown". Every comparison with it returns unknown, not false, and every aggregate quietly skips it.