Streamhub's marketing team wants a complete view of the free-tier user base and any purchases they have made.
Write a query to return every free-plan user alongside their conversion amounts. Users who have never converted must still appear, with the conversion column missing.
Assumptions:
- The
userstable contains every account on the platform; free-plan users are identified byplan = 'free'. - The
conversionstable records each paid conversion event;user_idon each conversion points to a user. - The plan condition applies to the user record. The result is anchored on the user side: every free-plan user appears, even if they have no conversion record.
Output:
- One row per free-user-conversion pair, plus one row per free user with no conversions, with columns
user_idandamount.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
u.id AS user_id,
cv.amount
FROM
users u
LEFT JOIN conversions cv ON u.id = cv.user_id
WHERE
u.plan = 'free'The shape
The filter u.plan = 'free' lives on the left (preserved) side of the LEFT JOIN. That's what protects the outer-join behavior: every free-plan user survives the WHERE, including the 20 free users who have never converted and whose cv.amount is NULL.
Clause by clause
SELECT u.id AS user_id, cv.amountreturns the user's ID from the left side and the conversion amount from the right side. For free users with no conversion record,cv.amountcomes back asNULL.FROM users u LEFT JOIN conversions cv ON u.id = cv.user_idpairs each user with each of their conversions. Users who have converted multiple times appear multiple times; users who have never converted appear once, withNULLincv.amount.WHERE u.plan = 'free'narrows the result to free-plan users only. The predicate references the left table, so it removes paid-plan users without touching the join's preservation behavior. Free users with no conversions still appear in the output withNULLincv.amount— exactly what the marketing team's "complete view" requires.
Why this and not WHERE u.plan = 'free' AND cv.amount IS NOT NULL
Adding the second predicate would drop the 20 never-converted free users from the result, collapsing the report into "free users who have paid at least once." That's a useful question, but it's not this question. The prompt explicitly anchors the row set on the user side: every free-plan user appears, conversion or no conversion.
The trap
The load-bearing trap with LEFT JOIN is putting a condition on the right table inside WHERE. Suppose the marketing team wanted to additionally filter to only $9 conversions and wrote:
SELECT u.id AS user_id, cv.amount
FROM users u
LEFT JOIN conversions cv ON u.id = cv.user_id
WHERE u.plan = 'free' AND cv.amount = 9The never-converted free users would silently disappear. Their cv.amount is NULL, and NULL = 9 evaluates to NULL, which WHERE treats as false. The query still uses the LEFT JOIN keywords, but the result is identical to an INNER JOIN. The bug is invisible at the SQL level — the keywords look right.
The rule is mechanical: predicates on the preserved-side columns go in WHERE. Predicates on the unpreserved-side columns that should still allow unmatched left rows belong in the ON clause, where they apply during the match phase rather than after. Mixing them up converts the outer join into an inner join in everything but name.
You practiced applying a condition only on the preserved (left) side of a LEFT JOIN so the row-preservation guarantee holds. The recurring shape any time a WHERE predicate must not collapse the outer join behavior into an inner one.
