Starting with just your inline tables, this code should work:
LEFT JOIN (offers) LOAD * RESIDENT purchases; data_final_temp: LOAD customer, offer_date, date(max(if(purchase_date<offer_date,purchase_date))) as MostRecentPurchase RESIDENT offers GROUP BY customer,offer_date ; DROP TABLES offers,purchases;
Thank you! For some reason it took me a while to see why this works. I will explain it here for others as well as myself (the best way to learn something is to teach it).
When doing a left join of purchases onto offers, the resulting offers table is as follows:
customer purchase_date offer_date A 1/1/2012 3/15/2012 A 9/1/2012 3/15/2012 A 1/1/2012 11/15/2012 A 9/1/2012 11/15/2012 B 5/1/2012 1/15/2012 B 5/1/2012 10/15/2012
Then, you are querying the resulting offers table for the highest value (max) of offer date for each customer and purchase date (group by), which results in the desired result.
customer offer_date MostRecentPurchase A 3/15/2012 1/1/2012 A 11/15/2012 9/1/2012 B 1/15/2012 B 10/15/2012 5/1/2012
Thanks for your help!