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
Table: customers email cid firstname.lastname@example.org 1001 email@example.com 1002 firstname.lastname@example.org 1003 Table : orders cid pid order_date 1001 4000 Aug-1-2021 1002 5000 Aug-1-2021 1003 4000 Aug-1-2021 Table : clicks cid pid click_date 1001 4000 Jul-1-2021 1002 8000 Jul-1-2021 1003 8000 Jul-1-2021 Table : product pid category_id 4000 1 8000 2 5000 2 Table : dim categorty_id category 1 Games 2 Books 3 Music
Expected results email cid category_clicked category_ordered email@example.com 1001 games games firstname.lastname@example.org 1002 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