Reconcile Users and Conversions — FULL OUTER JOIN in SQL

The problem

Streamhub's finance team needs a complete view of the user and conversion tables, in both directions:

Users who have never converted (conversion amount will be NULL).
Conversions with no user match (user ID will be NULL).

Write a query to return the user ID and conversion amount for every row in the combined view.

Assumptions:

The users table contains every account on the platform.
The conversions table records each paid conversion event.

Output:

One row per matched pair, plus one row per user with no conversions, plus one row per conversion with no user match, 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
  FULL OUTER JOIN conversions cv ON u.id = cv.user_id

The shape

A FULL OUTER JOIN between users and conversions keeps every row from both tables. Users who have converted appear with their amount; users who have never converted appear with amount as NULL; conversions whose user_id doesn't resolve to a real user appear with user_id as NULL. Three categories of rows, one query — exactly the cross-table reconciliation Streamhub's finance team is after.

Clause by clause

SELECT u.id AS user_id, cv.amount returns one column from each side. On matched rows both values are real. On a never-converted user, amount is NULL. On a conversion that points to no user, user_id is NULL. The NULL patterns are how finance reads which category each row belongs to.
FROM users u FULL OUTER JOIN conversions cv ON u.id = cv.user_id is the join. The ON condition pairs a user with each of their conversions. Where it matches, the row is assembled from both sides. Where it doesn't, the outer join preserves the row and NULL-pads the missing side — and FULL makes that guarantee in both directions, so neither table loses rows.
No WHERE. The team asked for the combined view of both tables, so every row the join produces belongs in the output.

You practiced FULL OUTER JOIN in a different domain. The same shape applies anywhere two datasets need to be compared as peers — neither one is the "primary," both contribute orphan rows to the result.

Return the user ID and conversion amount for every row in the combined view

The shape

Clause by clause

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