Get top 3 products per category. Find latest 5 orders per customer. Retrieve most recent status per entity. LATERAL enables row-by-row correlated operations.

E-commerce Product Recommendations

Show top 3 related products for each product based on purchase history.

LATERAL join to get related products per main product

SELECT p.product_name, related.related_name, related.similarity_score

FROM products p,

LATERAL (

SELECT rp.product_name as related_name, pr.similarity_score

FROM product_relationships pr

JOIN products rp ON pr.related_product_id = rp.product_id

WHERE pr.product_id = p.product_id

ORDER BY pr.similarity_score DESC

LIMIT 3

) related

WHERE p.status = 'active';

Enables personalized product recommendations, increasing cross-sell opportunities.

All