I have these below 5 tables and I am trying to find customers that ordered from the same category they clicked. With a condition where order date should be greater than the click date
: customers email cid a .com b .com c .com : orders cid pid order_date Aug Aug Aug : clicks cid pid click_date Jul Jul Jul : product pid category_id : dim categorty_id category Games Books Music
Expected results email cid category_clicked category_ordered a.com games games b .com books books
c@gmail will be excluded because he ordered a different category than the one he clicked. I am not able to get how to fetch the data based on the category. I was only able to write a query to fetch if customers bought the same item they clicked. Any help is appreciated. This is the query i have to find exactly what customer clicked & bought the same pid.
select distinct cust.email From customers cust Join orders o on cust.cid=o.cid join clicks c on c.cid=o.cid and c.pid=o.pid Join product prod ON c.pid=prod.pid inner join dim dim on dim.category_id=prod.category_id where o.order_date>=c.click_date