How to find the category that is both ordered & clicked by customer joining multiple tables?

Question

question

newbie12466 asked Aug 16, '21

How to find the category that is both ordered & clicked by customer joining multiple tables?

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         
    a@gmail.com     1001            
    b@gmail.com     1002            
    c@gmail.com     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
    a@gmail.com     1001    games               games
    b@gmail.com     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

query mysql sql query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-08-17T14:09:10Z

anthony.green answered Aug 17, '21

CREATE TABLE #customers (email varchar(20),cid int)
insert into #customers values
('a@gmail.com',1001),            
('b@gmail.com',1002),            
('c@gmail.com',1003)
CREATE TABLE #orders (cid int,pid int,order_date date)
INSERT INTO #orders values
(1001,4000,'2021-08-01'),    
(1002,5000,'2021-08-01'),    
(1003,4000,'2021-08-01')    
CREATE TABLE #clicks (cid int,pid int,click_date date)
insert into #clicks values
(1001,4000,'2021-07-01'),    
(1002,8000,'2021-07-01'),    
(1003,8000,'2021-07-01')    
CREATE TABLE #product (pid int,category_id int)
insert into #product values
(4000,1),           
(8000,2),           
(5000,2)           
CREATE TABLE #dim (category_id int,category varchar(10))
insert into #dim values
(1,'Games'),           
(2,'Books'),           
(3,'Music')

select cus.email, ord.order_date, clidim.category as clicked_category, orddim.category as ordered_category from #customers cus
inner join #orders ord
on cus.cid = ord.cid
inner join #clicks cli
on cus.cid = cli.cid
inner join #product ordprod
on ord.pid = ordprod.pid
inner join #product cliprod
on cli.pid = cliprod.pid
inner join #dim orddim
on ordprod.category_id = orddim.category_id
inner join #dim clidim
on cliprod.category_id = clidim.category_id
where clidim.category = orddim.category

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question