Aggregates collapse many rows into one value: count(*), sum(col), avg(col), min(col), max(col).
GROUP BY column makes one output row per distinct value, and the aggregate runs within each group. "Posts per user" is GROUP BY the user.
Rule: every column in SELECT must either be in the GROUP BY or be inside an aggregate.
WHERE filters rows before grouping. HAVING filters groups after. "Tags used at least 3 times" is a HAVING.
A plain JOIN drops rows with no match. A user with no posts vanishes from users JOIN posts. A LEFT JOIN keeps every row from the left table and fills the right side with NULL when there's no match.
To count including zeros: LEFT JOIN, then count(right_table.column). count(column) skips NULLs, count(*) doesn't.
To find what's missing: LEFT JOIN, then WHERE right_table.id IS NULL.
Aggregates run after the joins. If you join two "many" tables to the same parent (say a post's likes and its comments), every like is paired with every comment, so each count sees likes × comments rows and comes out inflated.
count(DISTINCT col) counts each different value once, so repeats don't matter.
A subquery is a query in parentheses used as a value: (SELECT count(*) FROM likes WHERE likes.post_id = posts.id) returns one number per post, so nothing gets multiplied.
Clause order: SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...