Interview Drills · JOIN + date difference
32. Fast converters
Concept: JOIN + date differenceshow
Join conditions can filter too: JOIN events p ON p.user_id = u.id AND p.event = 'purchase' picks only purchase rows. Subtracting two DATEs gives whole days, so 'within N days' is date_b - date_a <= N.
Example (different data):
SELECT a.name, t.closed_on - t.opened_on AS days_open FROM tickets t JOIN agents a ON a.id = t.agent_id AND t.priority = 'high';
Your task
Marketing wants to celebrate users who bought within their first week: anyone who purchased 7 days or less after signing up.
Return username and days_to_purchase (days from the user's created_at to their purchase event) for users who purchased within 7 days (day 7 counts). Fastest first, ties by username.
Starting Postgres in your browser…
Checking your access…