Is this possible with mysql?

Question

First of all: sorry for the title, but maybe I will find a better one later.

I asked this some minutes ago, but since I was not able to describe what I want I try it again :)

Here is my table structure: http://sqlfiddle.com/#!2/b25f9/37

The table is used to store user sessions.

Out of this I would like to generate a stacked bar chart that should show how many active users I have. My idea was that I group the users based on their online-times of the last days like this

Lets say its friday:

• Group B: Users that were online thursday (and today)
• Group C: Users that were not online thursday but wednesday (and today)
• Group D: Users that were not online thursday or wednesday but tuesday (and today)
• Group E: Users that were not online thursday, wednesday or tuesday but last monday, sunday or saturday (and today)

• Group A: Users that do not match the other groups (but were only today)

• I only want to know the number of users in those groups (for a specific day)

• a user can only be in ONE of these groups (for the same day)

Answer 1

Another Update: Accidently (by copy&paste) had starttime = ... or starttime = ... but it should be starttime = ... or endtime = ...

UPDATE:

To explain my query in more detail (in the final query there are even more comments):

First we simply got

SELECT
...
FROM gc_sessions s
WHERE DATE(starttime) = CURDATE() OR DATE(endtime) = CURDATE()

That's nothing more like saying "give me all users whose session started today or ended today". Having to consider those two times again and again makes the query a bit clumsy, but actually it's not that complicated.

So, usually we would use the COUNT() function to count something, obviously, but since we want "conditional counting", we simply use the SUM() function and tell it when to add 1 and when not.

SUM (CASE WHEN ... THEN 1 ELSE 0 END) AS a_column_name

The SUM() function examines now each row in the result set of sessions from today. So for each user in this result set we look if this user was online the date we specify. It doesn't matter how many times he/she was online, so for performance reasons we use EXISTS. With EXISTS you can specify a subquery which stops as soon as something is found, so it doesn't matter what it returns when something is found, as long as it's not NULL. So don't get confused why I selected 1. In the subquery we have to connect the user which is currently examined from the outer query with the user from the inner query (subquery) and specify the time window. If all criterias meet count 1 else 0 like explained before.

SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndYesterday,

Then we make a column for each condition and voila, you have all you need in one query. So with your updated question your criteria has changed, we just have to add more rules:

SELECT
/*this is like before*/
SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS FridayAndThursday,
SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 2 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 2 DAY)))
         /*this one here is a new addition, since you don't want to count the users that were online yesterday*/
         AND NOT EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 1 DAY)))
    THEN 1 ELSE 0 END) AS FridayAndWednesdayButNotThursday,
SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 3 DAY) /* minus 3 days to get tuesday*/
                       OR (date(endtime) = CURDATE() - INTERVAL 3 DAY)))
         /*this is the same as before, we check again that the user was not online between today and tuesday, but this time we really use BETWEEN for convenience*/
         AND NOT EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY)))
    THEN 1 ELSE 0 END) AS FridayAndTuesdayButNotThursdayAndNotWednesday,
.../*and so on*/
FROM gc_sessions s
WHERE DATE(starttime) = CURDATE() OR DATE(endtime) = CURDATE()

So, I hope you get the idea now. Any more questions? Feel free to ask.

end of update

Answer to previous version of question:

select 
SUM(CASE WHEN EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                      AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                           OR (date(starttime) = CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndYesterday,
SUM(CASE WHEN EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                      AND ((date(starttime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY) 
                           OR (date(starttime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndYesterdayOrTheDayBeforeYesterday,
SUM(CASE WHEN EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                      AND ((date(starttime) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY) 
                           OR (date(starttime) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndWithinTheLastWeek
from gc_sessions s
where date(starttime) = CURDATE()
or date(endtime) = CURDATE()

Answer 2

Example table:

CREATE TABLE `test`.`user_login_history` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `userid` INTEGER UNSIGNED NOT NULL,
  `date` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

Once a user login, check whether he/she has login today or not:

select count(*) from user_login_history where 
userid = 1 and `date` = '2013-01-28 00:00:00';

If the returned value is 1, means he/she has login today. no changes needed.
but, if the returned value is 0, means he/she has not login today. So record it down.

insert into user_login_history(userid,`date`)values(1,'2013-01-28 00:00:00');

Q1. How many users were online TODAY that were also online YESTERDAY?

select count(*) from user_login_history u where 
u.`date` = '2013-01-28 00:00:00' and 
(
select count(*) from user_login_history v where 
v.`date` = '2013-01-27 00:00:00' and 
v.userid = u.userid
) = 1;

Q2. How many users were online TODAY that were also online within in the last TWO DAYS

select count(*) from user_login_history u where 
u.`date` = '2013-01-28 00:00:00' and
(
select count(*) from user_login_history v where
v.`date` >= '2013-01-26 00:00:00' and
v.`date` <= '2013-01-27 00:00:00' and
v.userid = u.userid
) > 0;

Q3. How many users were online TODAY that were also online within the last 7 DAYS

select count(*) from user_login_history u where 
u.`date` = '2013-01-28 00:00:00' and
(
select count(*) from user_login_history v where
v.`date` >= '2013-01-21 00:00:00' and
v.`date` <= '2013-01-27 00:00:00' and
v.userid = u.userid
) > 0;

Answer 3

     SELECT today.user
          , GROUP_CONCAT(DISTINCT today.ip) ip
       FROM gc_sessions today
       JOIN gc_sessions yesterday
         ON DATE(yesterday.starttime) = DATE(today.starttime) - INTERVAL 1 DAY
        AND today.user = yesterday.user
      WHERE DATE(today.starttime) = '2013-01-10'
      GROUP 
         BY today.user;

Answer 4

You need to add a subquery that loads the data from the specified range (eg, 1day/2day/7days) and compares it with the data for the current day.

set @range = 7;
select * from gc_sessions
WHERE user in (SELECT user from gc_sessions
where starttime between subdate(current_date, @range) AND subdate(current_date, 1))

AND starttime > subdate(current_date, 0)

Where @range holds information about the number of days. See your expanded sql fiddle at - http://sqlfiddle.com/#!2/9584b/24

Answer 5

For yesterday

select id from gc_sessions where id in 
( 
  select id 
  from gc_sessions 
  where starttime > subdate(current_date, 2)
  and endtime < subdate(current_date, 1)
)

and starttime > subdate(current_date, 1);

For 2 Days

select id from gc_sessions where id in 
( 
  select id 
  from gc_sessions 
  where starttime > subdate(current_date, 3)
  and endtime < subdate(current_date, 1)
)

and starttime > subdate(current_date, 1);

For 7 Days

select id from gc_sessions where id in 
( 
  select id 
  from gc_sessions 
  where starttime > subdate(current_date, 8)
  and endtime < subdate(current_date, 1)
)

and starttime > subdate(current_date, 1);

Answer 6

Instead of relying on session table, I suggest you to create separate table, which stores 2 fields, date and user_id. Every time user logs-in you need to insert new entry into this table.

This way you will be able to retrieve all the 3 requirement of yours.