DB Exercises
0
← Exercises/
Joins•1 of 12
PrevNext
JoinsIntermediate

Detecting Dormant Accounts with LEFT JOIN

Scenario

Customer Success needs to identify accounts that have registered and paid for seats, but haven't logged any user events in the last 30 days. These "ghost accounts" are at extreme risk of churn during renewal.

Task
Find all customers who have **never logged any activity** in the `activity_events` table. Join `customers` to `activity_events` using a `LEFT JOIN`. Return: 1. `customer_id` 2. `company_name` 3. `account_manager` 4. `plan_name` Order the result by `customer_id` ascending.
Expected Output Format

List of customer_id, company_name, account_manager, and plan_name for customers with zero activity records.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Detecting Dormant Accounts with LEFT JOIN” SQL Problem

Business Scenario

Customer Success needs to identify accounts that have registered and paid for seats, but haven't logged any user events in the last 30 days. These "ghost accounts" are at extreme risk of churn during renewal.

Expected Output & Criteria

List of customer_id, company_name, account_manager, and plan_name for customers with zero activity records.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE customers (
        customer_id INT PRIMARY KEY,
        company_name VARCHAR(100) NOT NULL,
        account_manager VARCHAR(80) NOT NULL,
        plan_name VARCHAR(50) NOT NULL
      );

      CREATE TABLE activity_events (
        event_id INT PRIMARY KEY,
        customer_id INT NOT NULL REFERENCES customers(customer_id),
        event_name VARCHAR(50) NOT NULL,
        event_timestamp TIMESTAMP NOT NULL
      );

Target SQL Clauses & Operators

LEFT JOINIS NULL

Want more Joins practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Joins Track→