Joins & aggregation

SQL is the single most-tested analyst skill. Two mechanics carry most of it: joins (combine rows across tables) and GROUP BY aggregation (collapse rows into summaries). Get these reflexive and half the screen is won.

Join types

• INNER JOIN: only rows matching in both tables. The default and most common.
• LEFT JOIN: every row from the left table, plus matches from the right (NULL where none). Use to keep all customers even those with no orders.
• RIGHT / FULL OUTER: keep unmatched rows from the right / both sides. Rare but know they exist.
• CROSS JOIN: every combination (Cartesian product) — usually an accident, occasionally deliberate for date spines.

-- Revenue per customer, including those who never ordered
SELECT c.customer_id,
       c.name,
       COUNT(o.order_id)        AS num_orders,
       COALESCE(SUM(o.amount),0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC;