Find customers who never ordered (for marketing). Find products never sold (for inventory). Find employees without performance reviews (for HR). Anti-joins identify gaps in data.

Marketing Campaign - Inactive Customer Targeting

Marketing team needs to identify customers who registered but never made a purchase for a special welcome offer campaign.

Anti-join to find customers without orders, with segmentation by registration date

SELECT

c.customer_id,

c.customer_name,

c.email,

c.registration_date,

DATEDIFF(CURRENT_DATE, c.registration_date) as days_since_registration,

CASE

WHEN c.registration_date >= CURRENT_DATE - INTERVAL 7 DAY THEN 'New (0-7 days)'

WHEN c.registration_date >= CURRENT_DATE - INTERVAL 30 DAY THEN 'Recent (8-30 days)'

WHEN c.registration_date >= CURRENT_DATE - INTERVAL 90 DAY THEN 'Stale (31-90 days)'

ELSE 'Dormant (90+ days)'

END as customer_segment

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL

AND c.email_verified = 1

AND c.status = 'active'

ORDER BY c.registration_date DESC;

Enables targeted welcome campaigns with different messaging based on registration age. Converts inactive registrations into paying customers.

All

Inventory Management - Slow-Moving Product Analysis

Inventory team needs to identify products that have never sold to optimize warehouse space and plan clearance sales.

Anti-join to find unsold products with cost and space analysis

SELECT

p.product_id,

p.product_name,

p.category,

p.price,

p.cost_price,

p.stock_quantity,

(p.stock_quantity * p.cost_price) as inventory_value,

p.created_date,

DATEDIFF(CURRENT_DATE, p.created_date) as days_in_catalog,

w.warehouse_location,

w.storage_cost_per_unit

FROM products p