Drill 7 of 9 in Joining Tables
0

Joining Tables · LEFT JOIN · filter in ON vs WHERE

7. 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).