Drill 3 of 3 in Cloud software questions
0

Interview Question Bank · Anti-join with a date window · correlated MIN

3. Sign-ups who skipped Chat in their first 30 days

HardCloud softwareQuestion #262

Concept: Anti-join with a date window · correlated MINshow

'Did A but not B within N days' is an anti-join with the date window inside the subquery: NOT EXISTS (SELECT 1 FROM usage WHERE same user AND product = B AND used_on BETWEEN signup AND signup + N). Leaving the window out answers a different question (never used B at all). If you write it as a LEFT JOIN instead, the window conditions must go in ON, not WHERE, or the join turns back into an inner join.

Example (different data):

SELECT m.name
FROM members m
WHERE NOT EXISTS (
  SELECT 1 FROM class_bookings b
  WHERE b.member_id = m.id
    AND b.booked_on BETWEEN m.joined_on AND m.joined_on + 14
);

Your task

Nimbusworks wants to nudge people who signed up through Notes toward Chat. Chat lets guests join calls without an account, so some people used it before they signed up.

For users who signed up through Notes and had no Chat use from their signup_date through signup_date + 30 (both days included), return name, signup_date and first_chat_on (their earliest Chat use of any date, NULL if never). Sort by signup_date, then name.

Starting Postgres in your browser…

Checking your access…