CODEWITHSUNDEEP
mysqlsql
Reza
Reza

Reputation: 292

MySql query Script to count active users in every month

I need a query for Mysql to find number of active users for every month in a date range. Active user is a user that have a login log records in this month and the month before it (Both Months not only one of them).

here is a scripts for table and test data.

CREATE TABLE `logger` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `UserId` INTEGER NULL DEFAULT NULL,
  `loginDate` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `logger` (`UserId`,`loginDate`) VALUES
('1001','20151109'),
('1002','20151103'),
('1003','20151111'),
('1002','20151205'),
('1003','20151208'),
('1001','20160103'),
('1002','20160105');

I need a result like this for a range like from 20151201 to 20160201

------------------------------
year |month |users |activeUsers
2015 |12    |2     |2 
2016 |01    |2     |1 // only uid 1002 have activity in past month 20151205

Ready to use Online Table http://sqlfiddle.com/#!9/e9881

Upvotes: 4

Views: 1651

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can do this with a trick. Add one month from each date and then you can use union all and aggregation for the count:

select year(logindate), month(logindate), count(distinct userid)
from ((select logindate, userid
       from logger
      ) union all
      (select date_add(longdate, interval 1 month), userid
       from logger
      )
     ) l
group by year(logindate), month(logindate)
order by 1, 2;

EDIT:

Oh, I misunderstood the question. You need two months in a row to be an active user. I understood that a user login would make the user active for two months. Okay, you can solve this with a join or exists:

select year(l.logindate), month(l.logindate), count(distinct l.userid)
from logger l
where exists (select 1
              from logger l2
              where l2.userid = l.userid and
                    year(date_sub(l.logindate, interval 1 month)) = year(l2.logindate) and
                    month(date_sub(l.logindate, interval 1 month)) = month(l2.logindate)
             )
group by year(l.logindate), month(l.logindate);

Upvotes: 5

Related Questions