[SOLVED] Returning Users query

[subcool]

Hello, i'v been trying to get this query to work without much success.

 

The purpose of this query is to get the active users.

 

Example:

There are 3 users, A, B and C.

A uses his product on day 1 and 3

B uses his product on day 3 only

C uses his product on day 1 only

 

Now i want to know all active users between day 2 and 4, and as an extra statistic i want to know Who of those users that used their product between day 2 and 4 also used their product BEFORE day 2. These users are classified as "Returning users". In this example only user A would be a returning user cause he used this product on day 1 too.

 

Getting all active users is no problem for me at the moment, but getting the returning users is hard (i also need to know all new users, aka users that used their product for the first time in the set time period (day 2 to 4 in the example)).

 

This is the query i was trying, but for some reason it only outputs 7 rows of the same user even though there are 1000's of users.

 

SELECT x.log_date, x.mc_user_id AS x_userid, x.mc_id
FROM mc_log AS x
WHERE (x.`action` =  'WAP_GETMENU' OR x.`action` =  'MC_CATINFO')
AND x.mc_id =  '17'
AND x.mc_user_id <> ''
AND x.log_date BETWEEN  '2007/10/23 00:00:00' AND '2007/10/26 00:00:00'
AND EXISTS (
    	SELECT y.mc_user_id AS y_userid
FROM mc_log AS y
WHERE x.mc_user_id = y.mc_user_id
AND (y.`action` =  'WAP_GETMENU' OR y.`action` =  'MC_CATINFO')
AND y.mc_id =  '17'
AND y.log_date < '2007/10/23 00:00:00'
)

 

Any  help would be greatly appreciated

[fenway]

I don't think you mean EXISTS... that just a boolean.

[rajivgonsalves]

You can use the case statment in the where cause for particular sets

 

more information available at

 

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

[subcool]

It seems using the above query works... i was using it on the development database which didnt have the correct data :S