SQL: GROUP BY and JOIN

Every day, every level

Theo, the studio's data analyst, can count yesterday's gardens. Now Kai wants the count for every day this week, and Remy wants to know which player-made levels get played most.

Two tools do it: GROUP BY, which turns rows into groups, and JOIN, which combines two tables.

An aggregate function computes one result from many rows: COUNT, SUM, AVG, MAX, MIN. With GROUP BY, the PostgreSQL docs explain, you get one output row per group:

SELECT event_date, COUNT(*) AS defended FROM events WHERE event_name = 'garden_defended' GROUP BY event_date ORDER BY event_date;

Seven days, seven rows. AS names the new column.

Rows or players?

48,210 gardens were defended on 10 October. That is not 48,210 players: one player can defend ten gardens.

COUNT(*) counts rows. COUNT(DISTINCT player) counts the different players. PostgreSQL's docs say count(distinct x) gives the number of distinct non-null values of x. The same day had 9,400 different players.

WHERE first, HAVING after

The PostgreSQL docs: WHERE selects rows before groups and aggregates are computed. HAVING selects groups after. So an aggregate can't go in WHERE: the counts don't exist yet.

Levels shared more than 100 times:

SELECT level_id, COUNT(*) FROM events WHERE event_name = 'level_shared' GROUP BY level_id HAVING COUNT(*) > 100;

JOIN

A second table, levels, has one row per published level: level_id, creator, shard_count.

A join pairs rows from two tables using a condition after ON:

SELECT levels.level_id, levels.shard_count, events.player FROM events JOIN levels ON events.level_id = levels.level_id;

PostgreSQL calls it good style to put the table name before every column, so the query still works if both tables later get a column with the same name.

LEFT JOIN keeps the unmatched

A plain JOIN, an inner join, drops rows with no match. A level nobody has played has no events, so it vanishes.

A LEFT JOIN keeps every row of the table on the left at least once. Where the right table has no match, its columns are filled with NULL.

FROM levels LEFT JOIN events ON ... keeps every level. Then COUNT(events.player) gives 0 for unplayed ones, because counting a column skips NULLs.

From counts to journeys

With GROUP BY and JOIN, Theo can follow players step by step through their first week, and see where they stop.

Bolt says: Next: funnels.