Event Counts Across Left Joins — NULL Propagation in SQL

The problem

Scenario: Streamhub's product team is auditing user engagement depth and needs every user paired with their total event count, including users with no sessions and users whose sessions have no recorded events.

Task: Write a query to return each user's id and the total number of events they have generated across all of their sessions, with total_event_count reported as 0 for users with no events on record.

Assumptions:

A user's total_event_count is the count of events recorded across all of their sessions.
The result covers every user.
A user with no recorded events appears with total_event_count of 0.

Output:

One row per user.
Columns in this order: user_id, total_event_count.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  u.id AS user_id,
  COUNT(e.id) AS total_event_count
FROM
  users u
  LEFT JOIN sessions s ON s.user_id = u.id
  LEFT JOIN events e ON e.session_id = s.id
GROUP BY
  u.id

The shape

Two chained LEFT JOINs walk from users down through sessions to events, and COUNT(e.id) returns 0 for any user whose branch ran out before reaching an event. Counting the child id, rather than COUNT(*), is what makes the empty case land on zero instead of one.

Clause by clause

SELECT u.id AS user_id, COUNT(e.id) AS total_event_count returns one row per user with the count of events attached to them.
FROM users u LEFT JOIN sessions s ON s.user_id = u.id keeps every user in the result, even those with no sessions. For a sessionless user, every sessions column is NULL.
LEFT JOIN events e ON e.session_id = s.id extends the chain to events while preserving users whose sessions had no events. For those users, e.id is also NULL.
GROUP BY u.id collapses back to one row per user so COUNT runs per user.

The trap

COUNT(*) would return 1 for a user with no sessions, not 0. The LEFT JOIN left one row per user with all the right-side columns set to NULL, and COUNT(*) counts that row because it counts rows regardless of whether any column is NULL. COUNT(e.id) ignores NULL values, so the placeholder row contributes nothing and the user reports 0 events.

You practiced chaining two left-joins so users without sessions and sessions without events both reach the count step, where counting the child id naturally produces 0 for empty branches.

Return each user's `id` and the total number of `events` they have generated across all of their `sessions`, with `total_event_count` reported as `0` for users with no events on record

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.