Intermediate Joins · LEFT JOIN · filter in ON vs WHERE
19. Purchase date for every user
Concept: LEFT JOIN · filter in ON vs WHEREshow
With a LEFT JOIN, where you filter the right table matters. A condition in ON decides which rows match, and unmatched left rows still survive with NULLs. The same condition in WHERE runs after the join, and NULL fails it, so those rows vanish and you're back to an inner join.
Example (different data):
SELECT a.name, b.title AS bestseller FROM authors a LEFT JOIN books b ON b.author_id = a.id AND b.is_bestseller = true;
Your task
Growth wants every user listed with their purchase date, with a blank for anyone who hasn't bought yet, so they can target them.
Return username and purchase_date (the date of the user's purchase event, NULL if they never purchased) for all users. Sort by username.
Solution unlocks after 3 more tries
Starting Postgres in your browser…
query.sql
Output
Write a query and press Run Query (Ctrl/⌘ + Enter).