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
;