查询 - 在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 in this month.

user_id      visited_at                

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

1750        2015-04-06 10:39:20
1870        2015-04-05 15:48:11
1990        2015-04-04 12:38:35
1920        2015-04-03 10:18:21
1080        2015-04-01 10:18:21
1750        2015-01-28 12:38:59
1920        2015-01-19 17:20:20
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

Month               New        Repeat
----------------------------------------------
October 2013        1          0
April 2014          1          0
January 2015        1          2
April 2015          2          3

Answer 1

One approach is to get the date of the first visit for each user using a subquery. Then join in this information, and use count(distinct) to count the number of users:

select year(v.visited_at) as yyyy, month(visited_at) as mm,
       count(distinct user_id) as num_users,
       count(case when v.visited_at = vv.minva then user_id end) as num_new_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 year(v.visited_at), month(visited_at)
order by year(v.visited_at),  month(visited_at);

I note that this gives the total and new users; the repeats are the difference.