Joins
INNER JOIN keeps matching rows from both tables; LEFT JOIN keeps all left rows (NULLs where no match); RIGHT/FULL extend that. The join condition and key cardinality determine row counts — a common bug is fan-out from a one-to-many join inflating aggregates.
Aggregation & filtering
GROUP BY collapses rows into groups; aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarise each. WHERE filters rows before grouping; HAVING filters groups after. Logical processing order matters: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
Window functions
Window functions (ROW_NUMBER, RANK, SUM() OVER (...)) compute across a set of rows related to the current row WITHOUT collapsing them — perfect for running totals, rankings, and 'top-N per group'. They're a frequent senior-level differentiator.