CODEWITHSUNDEEP
mysql
user3210039
user3210039

Reputation: 11

Getting unique users for consecutive days

I have a task to find unique users from our table for cumilative days.

What I wanted to get is 

select count(distinct(userid))  from session_tab where 
date(session) = '2016-01-15 00:00:00'
union all
select count(distinct(userid))  from session_tab where 
date(session in ('2016-01-15 00:00:00','2016-01-16 00:00:00')
union all
select count(distinct(userid))  from session_tab where 
date(session in ('2016-01-15 00:00:00','2016-01-16 00:00:00','2016-01-16 00:00:00')

and so on...for next 30 days.

Can some one pls let me know how can this be achieved

Upvotes: 0

Views: 27

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

To get the next 30 days, go between CURDATE() and CURDATE() plus 30 days. After that, it's just a case of counting and grouping:

SELECT DATE(session), COUNT(DISTINCT userid)
  FROM session_tab
  WHERE DATE(session) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
  GROUP BY DATE(session)
  ORDER BY DATE(session);

Upvotes: 1

Related Questions