1
votes

I am trying to combine two queries that will allow me to say "a user did put an item in the cart on desktop' and then 'a user checked out on mobile' and tie them together by an ID. I am hoping to be able to get a list of IDs who did both, one after the other. If possible, I am hoping to get some insight into a general way to use SQL to say "this happened, here's the criteria, THEN this happened, here's the criteria". All columns for both queries can be found in the same 2 tables. I have tried to include my code as best I can without sharing company data.

WITH X AS(
SELECT
  1.ID
FROM 1
LEFT JOIN 2
  ON 1.ID = 2.ID 
    AND click IN ( ---Cart adds
      clickname1,   
      clickname2        
    )
WHERE 
  pageviews > 0
  AND 1.visit_dt BETWEEN CURRENT_DATE - 90 AND CURRENT_DATE - 1
  AND device = 'Mobile'
  GROUP BY 1)


SELECT ID
FROM 1 
JOIN 2 
  ON 1.ID = 2.ID
JOIN X
  ON X.ID = 2.ID
WHERE 
  clickname = clickname3 --submit order
 AND 1.visit_dt BETWEEN CURRENT_DATE - 90 AND CURRENT_DATE - 1
  AND device = 'Desktop'
--GROUP BY 1
;
It is unfortunate you cannot share queries, due to this, we are exceedingly unlikely to be able to help in any meaningful way. We'd be stabbing in the dark to helping fix a query we can't see (like trying to build Lego blindfolded) - Can O' Spam
I agree with @Can, in general, but look for a "sessionize" function in BQ. Not sure if it's there (or even if it helps your case), but being able to arrange actions into a sequence based on some rule or rules is what sessionize-ing data is all about - sounds like this would support your needs to some degree. - v0rl0n
Thank you for the feedback. I have tried to add my query and remove company info. Just to be clear on the logic, I'm trying to say that they added an item to the cart on mobile and then they checked out on their desktop. Same tables used, same ID to tie the shopper to both events. - New2SQL