SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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