Joins, set ops, aggregations, and grain

Most wrong numeric answers are grain mistakes: joining a fact to a duplicated dimension, then SUM. Before aggregating, write “one row per ___”.

• INNER JOIN — matching keys only
• LEFT JOIN — keep all left rows; unmatched right columns NULL
• RIGHT JOIN — keep all right rows
• FULL OUTER JOIN — keep both sides
• Filtering WHERE right.id IS NOT NULL after a left join turns it into an inner join

Multi-key joins: ON a.k1 = b.k1 AND a.k2 = b.k2.

• UNION — distinct rows
• UNION ALL — keep duplicates (usually faster when you know there are none)

COUNT(*), COUNT(col) (ignores NULL), approx_count_distinct, AVG / mean, and summary stats (MIN, MAX, STDDEV). COUNT(col) vs COUNT(*) is a real exam distinction.

SELECT region, COUNT(*) FROM customers ORDER BY region fails the requirement “customers per region” because it is missing GROUP BY region.

sqlSELECT region, COUNT(*) AS number_of_customers
FROM catalog.gold.customers
GROUP BY region
ORDER BY region;

SQL language