MediumJoins

Dormant Customer Churn & Inactivity Detection

Frequently Asked By:NetflixSpotifyStripe

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Given a customers table and an activity_logs table, write a query to identify all customers who registered at least 90 days ago but have logged zero activity events in the last 60 days.

Why Technical Interviewers Ask This

Anti-joins are ubiquitous in retention engineering. Interviewers want to see if you instinctively reach for LEFT JOIN ... WHERE IS NULL or NOT EXISTS rather than inefficient NOT IN (which fails catastrophically if the subquery returns even a single NULL).

Common Pitfalls & Interview Traps

Using NOT IN (SELECT customer_id FROM activity_logs) — if any row in activity_logs has customer_id IS NULL, the entire NOT IN expression evaluates to UNKNOWN and returns 0 rows.

Filtering the right table in the WHERE clause instead of the ON clause, inadvertently converting the LEFT JOIN into an INNER JOIN.

Misinterpreting rolling date windows with static dates.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

SELECT
  c.customer_id,
  c.name,
  c.created_at::date AS signup_date
FROM customers c
LEFT JOIN activity_logs a
  ON c.customer_id = a.customer_id
  AND a.activity_timestamp >= CURRENT_DATE - INTERVAL '60 days'
WHERE c.created_at <= CURRENT_DATE - INTERVAL '90 days'
  AND a.activity_id IS NULL
ORDER BY c.created_at ASC;

Approach Breakdown

We filter recent activities inside the ON clause of the LEFT JOIN. If a customer has no events within the last 60 days, the right side returns NULL. Filtering WHERE a.activity_id IS NULL retains precisely the dormant customers.

Edge Cases to Verbally Clarify with Your Interviewer

Customers with NULL values in foreign keys.

Brand new signups under 90 days old (properly filtered out by created_at condition).

Customers who were active 61 days ago (correctly identified as dormant).

Frequently Asked Follow-Up Questions

Why is NOT IN dangerous when handling NULL values in SQL?

In three-valued logic (TRUE, FALSE, UNKNOWN), `x NOT IN (1, 2, NULL)` evaluates to `x <> 1 AND x <> 2 AND x <> NULL`. Since any comparison with NULL yields UNKNOWN, the entire condition evaluates to UNKNOWN, filtering out all rows. NOT EXISTS or LEFT JOIN avoids this bug.