I'm have 2 tables: table for customers and table for activity. Im trying to find how many customers have 2 activity specific. For example, how many customers was bought and arrived to the branch. (i have line in the activity table for every act that the customer did.) this is the query:
SELECT COUNT(*) as total FROM activity
WHERE activity = 'arrived' AND
customerid IN
(SELECT DISTINCT(customerid) FROM activity
WHERE activity = 'bought')
but i need to know how much bought AFTER they arrvied, because there are customers that bought from the website, than arrived to the branch after 1 year for example. so i want only the customer that arrived and than bought. i tried this:
SELECT COUNT(*) as total, daten as odaten FROM activity
WHERE activity = 'arrived' AND
customerid IN
(SELECT DISTINCT(customerid), daten as tdaten FROM activity
WHERE activity = 'bought') HAVING odaten < tdaten
but its not working.. any idea?