dbdbexercises
Start free
← Problems|

7. Sales Leads That Never Converted

MediumJoinsPostgreSQL 18
Table: leads
+--------------+--------------+
| Column Name  | Type         |
+--------------+--------------+
| lead_id      | int          |
| company_name | varchar(100) |
| source       | varchar(30)  |
| created_at   | date         |
+--------------+--------------+

lead_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Table: accounts
+----------------+--------------+
| Column Name    | Type         |
+----------------+--------------+
| account_id     | int          |
| company_name   | varchar(100) |
| source_lead_id | int          |
| signed_at      | date         |
+----------------+--------------+

account_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Marketing is planning a re-engagement campaign for leads that never became paying accounts. Last time, the list came back empty and the campaign was cancelled, even though the sales team knew plenty of leads had gone quiet.

Every potential customer starts as a row in leads. When a lead signs up, a row is created in accounts with source_lead_id set to that lead's lead_id. Some accounts signed up directly without ever being a lead. Find every lead that has never converted into an account. Return: 1. lead_id 2. company_name 3. source Order by lead_id ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Every lead with no matching account (no account whose source_lead_id equals the lead_id), with lead_id, company_name and source.

Constraints:
  • Query must utilize clause(s): WHEREORDER BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Database: PostgreSQL 18, running in your browser.

About this problem

The business question

Marketing is planning a re-engagement campaign for leads that never became paying accounts. Last time, the list came back empty and the campaign was cancelled, even though the sales team knew plenty of leads had gone quiet.

Expected result: Every lead with no matching account (no account whose source_lead_id equals the lead_id), with lead_id, company_name and source.

What you will practice

WHEREORDER BY

Tables

CREATE TABLE leads (
    lead_id INT PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    source VARCHAR(30) NOT NULL,
    created_at DATE NOT NULL
  );

  CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    source_lead_id INT REFERENCES leads(lead_id),
    signed_at DATE NOT NULL
  );

More Joins practice

20 problems in this topic, with key concepts and common mistakes.

View topic