Finding count for a Period in sql

Question

I have a table with :

 user_id | order_date 
---------+------------
      12 | 2014-03-23
      12 | 2014-01-24
      14 | 2014-01-26
      16 | 2014-01-23
      15 | 2014-03-21
      20 | 2013-10-23
      13 | 2014-01-25
      16 | 2014-03-23
      13 | 2014-01-25
      14 | 2014-03-22

A Active user is someone who has logged in last 12 months. Need output as

Period | count of Active user
----------------------------
Oct-2013 - 1 
Jan-2014 - 5 
Mar-2014 - 10

The Jan 2014 value - includes Oct -2013 1 record and 4 non duplicate record for Jan 2014)

Answer 1

You can use a variable to calculate the running total of active users:

SELECT Period,
       @total:=@total+cnt AS `Count of Active Users`
FROM (       
SELECT CONCAT(MONTHNAME(order_date), '-', YEAR(order_date)) AS Period,
       COUNT(DISTINCT user_id) AS cnt       
FROM mytable 
GROUP BY Period
ORDER BY YEAR(order_date), MONTH(order_date) ) t,
(SELECT @total:=0) AS var

The subquery returns the number of distinct active users per Month/Year. The outer query uses @total variable in order to calculate the running total of active users' count.

Fiddle Demo here

Answer 2

I've got two queries that do the thing. I am not sure which one's the fastest. Check them aginst your database:

SQL Fiddle

Query 1:

select per.yyyymm,
(select count(DISTINCT o.user_id) from orders o where o.order_date >= 
(per.yyyymm - INTERVAL 1 YEAR) and o.order_date < per.yyyymm + INTERVAL 1 MONTH) as `count`
from
(select DISTINCT LAST_DAY(order_date) + INTERVAL 1 DAY - INTERVAL 1 MONTH as yyyymm
from orders) per
order by per.yyyymm

Results:

|                    yyyymm | count |
|---------------------------|-------|
| October, 01 2013 00:00:00 |     1 |
| January, 01 2014 00:00:00 |     5 |
|   March, 01 2014 00:00:00 |     6 |

Query 2:

select DATE_FORMAT(order_date, '%Y-%m'),
(select count(DISTINCT o.user_id) from orders o where o.order_date >= 
 (LAST_DAY(o1.order_date) + INTERVAL 1 DAY - INTERVAL 13 MONTH) and 
 o.order_date <= LAST_DAY(o1.order_date)) as `count`
from orders o1
group by DATE_FORMAT(order_date, '%Y-%m')

Results:

| DATE_FORMAT(order_date, '%Y-%m') | count |
|----------------------------------|-------|
|                          2013-10 |     1 |
|                          2014-01 |     5 |
|                          2014-03 |     6 |

Answer 3

The best thing I could do is this:

SELECT Date, COUNT(*) as ActiveUsers
FROM 
(
    SELECT DISTINCT userId, CONCAT(YEAR(order_date), "-", MONTH(order_date)) as Date
    FROM `a` 
    ORDER BY Date
)
AS `b`
GROUP BY Date

The output is the following:

|    Date | ActiveUsers |
|---------|-------------|
| 2013-10 |           1 |
|  2014-1 |           4 |
|  2014-3 |           4 |

Now, for every row you need to sum up the number of active users in previous rows. For example, here is the code in C#.

int total = 0;
while (reader.Read())
{ 
    total += (int)reader['ActiveUsers'];
    Console.WriteLine("{0} - {1} active users", reader['Date'].ToString(), reader['ActiveUsers'].ToString());
}

By the way, for the March of 2014 the answer is 9 because one row is duplicated.

Answer 4

Try this, but thise doesn't handle the last part: The Jan 2014 value - includes Oct -2013

select TO_CHAR(order_dt,'MON-YYYY'), count(distinct User_ID ) cnt from [orders] 
where User_ID  in 
(select User_ID from
 (select a.User_ID from  [orders] a,
(select a.User_ID,count (a.order_dt) from [orders] a 
where a.order_dt > (select max(b.order_dt)-365 from [orders] b where a.User_ID=b.User_ID)
group by a.User_ID
having count(order_dt)>1) b
where a.User_ID=b.User_ID) a
)
group by TO_CHAR(order_dt,'MON-YYYY');

Answer 5

This is what I think you are looking for

SET @cnt = 0;
SELECT Period, @cnt := @cnt + total_active_users AS total_active_users
FROM (
  SELECT DATE_FORMAT(order_date, '%b-%Y') AS Period , COUNT( id) AS total_active_users
  FROM t
  GROUP BY DATE_FORMAT(order_date, '%b-%Y')
  ORDER BY order_date
) AS t

This is the output that I get

Period      total_active_users
Oct-2013    1
Jan-2014    6
Mar-2014    10

You can also do COUNT(DISTINCT id) to get the unique Ids only

Here is a SQL Fiddle