在MySql中按日期查找新客户和重复客户

Question

I have a visit table with user_id and visited_at columns. I would like to find how many are new and repeat customer on each day in this month.

user_id      visited_at                

--------------------------------------------------------------------------

1750        2015-01-28 12:38:59
1920        2015-01-19 17:20:20
1009        2015-01-17 18:20:12
1920        2015-01-17 15:10:10
1080        2015-01-13 20:18:41
1920        2014-04-04 10:31:15
1750        2013-10-04 10:39:20

In January 2015, user 1750 and 1920 visited the same place so total repeated customers are 2. In April 2015, user 1750, 1920 and 1080 visited the same place so total repeated customers are 3. The output should be something like this

October 2013

Month               New        Repeat
----------------------------------------------
2013-10-04          1          0

April 2014

Month               New        Repeat
----------------------------------------------
2014-04-04          1          0

January 2015

Month               New        Repeat
----------------------------------------------
2015-01-13          1          0
2015-01-17          1          1
2015-01-19          0          1
2015-01-28          0          1

Answer 1

Chopra . . . I actually did misunderstand the question you posed in the comment. This is very similar to the structure by month, just the aggregation is by date instead:

select date(v.visited_at),
       count(case when v.visited_at = vv.minva then user_id end) as num_new_users,
       (count(distinct user_id) - count(case when v.visited_at = vv.minva then user_id end)
       ) as num_repeat_users
from visits v join
     (select user_id, min(visited_at) as minva
      from visits t
      group by user_id
     ) vv
     on v.user_id = vv.user_id
group by date(v.visited_at)
order by date(v.visited_at);