Imagine a sequence of actions. Each action is of a certain type.
Grouping by a given time-frame (e.g. day), how many of these actions happened for the first time, and how many were recurring?
Example Input:
+-----------+-------------+-------------------+
| user_id | action_type | timestamp |
+-----------+-------------+-------------------+
| 5 | play | 2014-02-02 00:55 |
| 2 | sleep | 2014-02-02 00:52 |
| 5 | play | 2014-02-02 00:42 |
| 5 | eat | 2014-02-02 00:31 |
| 3 | eat | 2014-02-02 00:19 |
| 2 | eat | 2014-02-01 23:52 |
| 3 | play | 2014-02-01 23:50 |
| 2 | play | 2014-02-01 23:48 |
+-----------+-------------+-------------------+
Example Output
+------------+------------+-------------+
| first_time | recurring | day |
+------------+------------+-------------+
| 4 | 1 | 2014-02-02 |
| 3 | 0 | 2014-02-01 |
+------------+------------+-------------+
Explanation
On 2014-02-02, users 2, 3, and 5 performed various different actions. There were 4 instances were the users performed an action for the first time; in one case the user 5 repeated the action 'play'.
I added a column 'Total Actions' because as I said, I believe there is a misinterpretation of facts in output example. You can remove it easily.
TEST in SQLFiddle.com for SQL Server 2008.
select
COUNT(q.repetitions) 'first time',
SUM(case when q.repetitions>1 then q.repetitions-1 else 0 end) as 'recurring',
day
from (
select COUNT(i.action_type) as 'repetitions',convert(date,i.time_stamp) as 'day'
from input i
group by i.user_id, i.action_type,convert(date,i.time_stamp)
) q
group by q.day
order by day desc