Find customers who have orders (EXISTS). Find products in active categories (EXISTS). Filter users with specific permissions (EXISTS). Semi-joins are efficient for existence checks.

Customer Segmentation - Active Buyers

Marketing needs to identify active customers who have made purchases in specific categories for targeted campaigns.

EXISTS pattern to filter customers based on purchase behavior

-- Find customers who bought Electronics in last 90 days

SELECT

c.customer_id,

c.customer_name,

c.email,

c.customer_tier,

c.registration_date

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

INNER JOIN order_items oi ON o.order_id = oi.order_id

INNER JOIN products p ON oi.product_id = p.product_id

WHERE o.customer_id = c.customer_id

AND p.category = 'Electronics'

AND o.order_date >= CURRENT_DATE - INTERVAL 90 DAY

AND o.status = 'completed'

)

AND c.status = 'active'

ORDER BY c.customer_tier DESC, c.registration_date;

Enables targeted marketing campaigns for electronics buyers. Higher conversion rates through relevant product recommendations.

All

Inventory Optimization - Products with Demand

Inventory team needs to identify products that have consistent demand to optimize stock levels and purchasing decisions.

EXISTS to find products with recent sales activity

-- Products with sales in last 3 months across multiple orders

SELECT

p.product_id,

p.product_name,

p.category,

p.price,

i.current_stock,

i.reorder_point

FROM products p

INNER JOIN inventory i ON p.product_id = i.product_id

WHERE EXISTS (

SELECT 1 FROM order_items oi