tsql - Complex SQL aggregation -

i have dataset analogously looks this:

x | u | datetime ------------- 1 | 1 | 1/1/12 1 | 2 | 1/1/12 1 | 2 | 1/1/12 1 | 2 | 1/1/12 1 | 4 | 1/1/12 2 | 2 | 2/1/12 2 | 3 | 2/1/12 1 | 3 | 3/1/12 2 | 4 | 3/1/12 3 | 2 | 4/1/12 

it log of visits. x id of thing visited, , u user id

i need compute 2 statistics.

given value x (x):

1) "new visitors": count number of unique users made first visit x x.

use cases:

• a user has visited x once -> counts 1
• a user has visited !x once -> counts 0
• a user has visited x twice -> counts 1
• a user has visited !x twice -> counts 0
• a user has visited many xs, first visit of x x -> counts 1
• a user has visited many xs, first visit of x !x -> counts 0

examples above data:

x | count --------- 1 | 3 2 | 1 3 | 0 

2) "returning visitors": count number of unique users have visited x more once or have visited x once, have visited x (i.e. visits made after single visit x not count)

examples above data:

x | count --------- 1 | 3 2 | 2 3 | 1  

i'm using sql server 2008, appreciated. thanks!

update

this appears answer q1, although not fast :(

select x.x, count(1) (     select t1.x     @t t1     group t1.x, t1.u     having (select count (1) @t t2 t2.u= t1.u , t2.occurredon < min(t1.occurredon)) =0  ) x  group x.x 

update 2

i think (2)

select t.x, count(1) @t t left join (     select t.u, min(t.occurredon) o     @t t     group t.u ) x on t.u = x.u , t.occurredon <= x.o x.u null group t.x 

for first case, need sub-query join filter out user-thing visits aren't first of kind. you'll have like

select x, count(*) [first visits] table t1      join (select u, min(datetime) firstvisit             table             group u) t2 on t1.datetime = t2.firstvisit , t1.u = t2.u group x 

edit: think solution second 1 fine, except join faster if replace <= =.