GROUP BY turns many rows into one row per group, and aggregates like COUNT, SUM and AVG work out each group's numbers. Every column in SELECT that isn't inside an aggregate has to be in GROUP BY.
Snowflake has a shortcut: GROUP BY ALL groups by every non-aggregate column in the SELECT list, so you don't have to repeat them.
HAVING filters groups after they're counted, as in HAVING COUNT(*) > 5. WHERE filters rows before.
Two Snowflake aggregates save a lot of typing. COUNT_IF(condition) counts the rows where the condition is true. LISTAGG(column, ', ') joins a group's values into one string, and WITHIN GROUP (ORDER BY column) sets the order.