Date Range JOIN - Product Pricing History

basic

Join orders with correct product prices based on order date

-- Price history table with date ranges

-- Join orders with prices valid at order time

SELECT

o.order_id,

o.order_date,

p.product_name,

ph.price,

ph.start_date,

ph.end_date

FROM orders o

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

INNER JOIN price_history ph ON o.product_id = ph.product_id

AND o.order_date >= ph.start_date

AND o.order_date <= ph.end_date

WHERE o.order_date >= '2024-01-01'

ORDER BY o.order_date;

Shows orders with the correct historical prices that were active at order time

Complex ON clause with equality (product_id) and range conditions (date BETWEEN). Finds price valid at order time.

All

Multiple Conditions with OR Logic - Customer Segmentation

intermediate

Join customers with promotions based on multiple eligibility criteria

-- Join customers with applicable promotions

-- Multiple OR conditions for different customer segments

SELECT

c.customer_name,

c.total_spent,

c.registration_date,

pr.promotion_name,

pr.discount_percent

FROM customers c

INNER JOIN promotions pr ON (

(pr.customer_type = 'new' AND c.registration_date >= CURRENT_DATE - INTERVAL 30 DAY)

OR

(pr.customer_type = 'vip' AND c.total_spent > 5000)

OR

(pr.customer_type = 'loyalty' AND c.loyalty_points > 1000)