Postgres - Lateral Join
Today I have another Postgres feature to eschew wonder and adulation for. The lateral Join.
While other databases also support lateral joins; the past half a decade I have been using mostly postgres. It's just what people have been paying me for.
Select Highest recent sales
SELECT customers.name, customers.contact_info, top_sales.* FROM customers LEFT JOIN LATERAL ( SELECT customer_id, amount, category, created_at FROM sales WHERE customer_id = customers.id ORDER BY amount DESC LIMIT 5 ) AS top_sales ON true
What does it do?
In-short, this is a way to get a report of all customers, with their greatest purchases. I've included a created_at column which can be used inside the inner-query, or just for display purposes. This sort of insight can answer important questions you may have about a customer; and can be used with other types of data to allow getting a limited subset of joined data.
I Hope you found this useful.