All Users and Their Conversions — LEFT JOIN and RIGHT JOIN in SQL

The problem

Streamhub's customer success team needs a full view of the user base alongside any revenue on record.

Write a query to return the user ID and conversion amount for every user, including users who have never converted (their conversion amount will be NULL).

Assumptions:

A user with multiple conversions appears once per conversion in the result; a user with zero conversions appears once with NULL in the conversion column.

Output:

One row per user-conversion pair, plus one row per user with no conversions, with columns user_id and amount.

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,
  cv.amount
FROM
  users u
  LEFT JOIN conversions cv ON u.id = cv.user_id

The shape

users is the dimension to enumerate in full; conversions is the fact table that may or may not have a record for any given user. A LEFT JOIN from users to conversions keeps every user and surfaces the never-converted ones as rows with NULL in amount.

Clause by clause

SELECT u.id AS user_id, cv.amount returns the user's ID, aliased so the column header carries the domain meaning, alongside the conversion amount from the right table. For users with no conversion record, cv.amount is NULL.
FROM users u is the anchor — every account on Streamhub's platform.
LEFT JOIN conversions cv ON u.id = cv.user_id pairs each user with each of their conversions. A user who converted three times appears three times; a user who has never converted appears once, with cv.amount as NULL. The 20 trailing rows in the result with amount: null are exactly those never-converted users.

Why this and not `INNER JOIN`

The team wants a full view of the user base alongside any revenue on record. The word that does the work is any. INNER JOIN would silently drop every user without a conversion, collapsing the question from "who are all our users and what have they paid" to "who has paid." Those are different reports. LEFT JOIN keeps the unmatched left rows and signals the absence with NULL on the right-side columns — the conversion-free users stay in the output, and their amounts are just missing.

You practiced LEFT JOIN against an event table where some entities have no events. The recurring shape: a LEFT JOIN from a dimension to a fact table is how you surface entities with zero activity — they appear as a single row with NULL in every fact-side column.

Return the user ID and conversion amount for every user, including users who have never converted (their conversion amount will be `NULL`)

The shape

Clause by clause

Why this and not `INNER JOIN`

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

The shape

Clause by clause

Why this and not INNER JOIN

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

Why this and not `INNER JOIN`