Price history tables join on date ranges. Inventory joins on quantity thresholds. Promotions join on date and customer segments. Audit logs join on time windows.

E-commerce Dynamic Pricing System

Online store needs to apply correct prices based on customer segments, promotional periods, and inventory levels. Different customers see different prices based on membership level and purchase history.

Complex joins with multiple conditions for customer segmentation and time-based pricing

-- Dynamic pricing based on customer tier and promotion eligibility

SELECT

p.product_name,

c.customer_name,

c.customer_tier,

CASE

WHEN pr.promotion_id IS NOT NULL THEN pr.discounted_price

WHEN c.customer_tier = 'premium' THEN p.base_price * 0.9

WHEN c.customer_tier = 'gold' THEN p.base_price * 0.95

ELSE p.base_price

END as final_price

FROM products p

CROSS JOIN customers c

LEFT JOIN promotions pr ON (

p.product_id = pr.product_id

AND (

(pr.customer_tier = c.customer_tier)

OR

(pr.customer_tier = 'all' AND c.total_spent >= pr.min_spend_requirement)

)

AND CURRENT_TIMESTAMP BETWEEN pr.start_date AND pr.end_date

AND pr.status = 'active'

)

WHERE p.status = 'active'

AND c.status = 'active'

ORDER BY c.customer_name, p.product_name;

Enables personalized pricing strategies, targeted promotions, and dynamic revenue optimization based on customer value and market conditions.

All

Financial Services Risk Assessment

Bank needs to assess loan applications by joining customer data with risk factors, credit history, and regulatory requirements based on loan amount ranges and customer profiles.

Multi-condition joins for risk scoring and regulatory compliance

-- Risk assessment with complex eligibility criteria

SELECT

la.application_id,

c.customer_name,

la.loan_amount,