DB Exercises
0
← Exercises/
Fundamentals•2 of 12
PrevNext
FundamentalsBeginner

Clean Marketing Lead Ingestion

Scenario

The demand generation team is auditing organic and paid acquisition leads. Some lead records contain missing utm_source values or duplicate records from retargeting pixels. They need a pristine list of unique, valid acquisition channels and countries.

Task
Query the `leads` table to extract unique combinations of `acquisition_channel` and `country_code`. Requirements: - Exclude records where `acquisition_channel` is `NULL` or marked as `'unknown'`. - Exclude records where `country_code` is `NULL`. - Order the output by `acquisition_channel` ascending, then by `country_code` ascending.
Expected Output Format

Unique list of acquisition_channel and country_code with no null or unknown values, alphabetically sorted.

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 “Clean Marketing Lead Ingestion” SQL Problem

Business Scenario

The demand generation team is auditing organic and paid acquisition leads. Some lead records contain missing utm_source values or duplicate records from retargeting pixels. They need a pristine list of unique, valid acquisition channels and countries.

Expected Output & Criteria

Unique list of acquisition_channel and country_code with no null or unknown values, alphabetically sorted.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE leads (
        lead_id INT PRIMARY KEY,
        email VARCHAR(120) NOT NULL,
        acquisition_channel VARCHAR(50),
        country_code VARCHAR(3),
        lead_score INT NOT NULL
      );

Target SQL Clauses & Operators

DISTINCTWHEREORDER BY

Want more Fundamentals practice?

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

View Fundamentals Track→