WHERE subqueries power everyday filtering: finding customers who placed orders, products in active categories, employees in high-performing departments, students enrolled in courses, accounts with transactions, users with permissions. They enable complex filtering based on related data without complex JOINs.

E-commerce: Customer Segmentation

Marketing needs to segment customers: active (placed orders in last 90 days), inactive (have orders but not recent), and never-ordered (no orders ever). Each segment gets different campaign.

-- Active customers (orders in last 90 days)

SELECT c.customer_id, c.name, c.email, 'Active' AS segment

FROM customers c

WHERE EXISTS (

SELECT 1

FROM orders o

WHERE o.customer_id = c.customer_id

AND o.order_date > CURRENT_DATE - INTERVAL '90 days'

)

UNION ALL

-- Inactive customers (have orders but not recent)

SELECT c.customer_id, c.name, c.email, 'Inactive' AS segment

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

)

AND NOT EXISTS (

SELECT 1

FROM orders o

WHERE o.customer_id = c.customer_id

AND o.order_date > CURRENT_DATE - INTERVAL '90 days'

)

UNION ALL

-- Never ordered

SELECT c.customer_id, c.name, c.email, 'Never Ordered' AS segment

FROM customers c

WHERE NOT EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

);

Segmented 250,000 customers: 85,000 active, 120,000 inactive, 45,000 never-ordered. Active segment: upsell campaign, 12% conversion. Inactive segment: win-back campaign with 15% discount, 8% reactivation. Never-ordered: onboarding email series, 5% first purchase. Total revenue impact: $1.8M. Query optimized with index on orders(customer_id, order_date) - runs in 850ms.

All

SaaS: Feature Adoption Analysis

Product team needs to identify accounts not using key features to target for training and support.

-- Accounts not using premium features

SELECT

a.account_id,

a.account_name,