Here is my stats with 100,000+ row sample data using same date interval as original

(8 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0.
Table 'DailyVisits'. Scan count 2, logical reads 10.

I totally spaced out Pesomannen teaser which definitely is challenging. Studiomoderna's first solution's Statistics IO show's Table 'DailyVisits'. Scan count 3, logical reads 6. We can bring it down to Table 'DailyVisits'. Scan count 2, logical reads 4 by changing the logic in the CTE to not use DailyVisits table. All other alternative solutions that I have tried have Scan count 2, logical reads 4 as I am selecting from DailyVisits atleast twice. Having said that, I am very curious to see your solution Pesomannen. It would be some good learning. Thanks

Studiomoderna, give it a new try.
Think about it, is there a way to minimize the number of joins?
Is there another way to get rid of the distinct aggregate?

two solutions:

-- SOLUTION 1 Tomaz Kastrun (tomaz.kastrun@studio-moderna.com)
;with dt_cte as
-- insert empty/missing dates
(select
min(dt) as dt_
,max(dt) as dt_m
from dailyvisits
union all
select
dt_+1 as dt_
,dt_m
from dt_cte
where
dt_+1 <= dt_m)
-- calculating added, removed and remained users
select
date_.dt_ as date
,count(distinct dv.visitor) as numvisits
,count(distinct dv.visitor)-(sum( case when (dv1.visitor = dv.visitor and dv.visitor is not null and dv1.visitor is not null) then 1 else 0 end)) as added
,count(distinct dv1.visitor) - sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as removed
,sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as remained

from
dt_cte as date_
left hash join dailyvisits as dv
on date_.dt_ = dv.dt
left hash join dailyvisits as dv1
on date_.dt_ = dateadd(day,1,dv1.dt)
group by date_.dt_



-- SOLUTION 2 Tomaz Kastrun
-- calculating added, removed and remained users
select
date_.dt_ as date
,count(distinct dv.visitor) as numvisits
,count(distinct dv.visitor)-(sum( case when (dv1.visitor = dv.visitor and dv.visitor is not null and dv1.visitor is not null) then 1 else 0 end)) as added
,count(distinct dv1.visitor)-sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as removed
,sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as remained

from
-- insert empty/missing dates
(
select
(select min(dt) from dailyvisits)+number as dt_
from master..spt_values as b with (nolock readuncommitted)
where
[type] = 'P'
and number between 1 and (select datediff(day,(select min(dt) from dailyvisits), (select max(dt) from dailyvisits)))
) as date_
left hash join dailyvisits as dv
on date_.dt_ = dv.dt
left hash join dailyvisits as dv1
on date_.dt_ = dateadd(day,1,dv1.dt)
group by date_.dt_


but I still can not beat Pesomannen teaser :)

another version with recursive CTEs, MAXRECURSION can be modified if the date range spans across more than 100 days

use SimTest
go

Declare @Start date
Declare @End date
Set @Start = '2011-06-01'
Set @End = '2011-06-08';

Declare @DailyVisits table
(
dt date,
visitor char(1)
)
insert into @DailyVisits
Select * from DailyVisits

;with DailyVisist_CTW(dt) as
(
select @Start as dt

union all

select
dateadd(DD, 1, dt) as dt
from DailyVisist_CTW
where dt < @End
)

Select
dt,
Visitor = (select count(visitor) from @DailyVisits where dt = d1.dt),
Added = (
Select COUNT(*) from
(
select visitor
from @DailyVisits where dt = d1.dt
and visitor not in
(
select visitor
from @DailyVisits where dt = DATEADD(dd, -1, d1.dt)
)
) a
),
Removed = (
Select COUNT(*) from
(
select visitor
from @DailyVisits where dt = DATEADD(dd, -1, d1.dt)
EXCEPT
select visitor
from @DailyVisits where dt = d1.dt
) a
),
Remained = (
Select COUNT(*) from
(
select visitor
from @DailyVisits where dt = d1.dt
INTERSECT
select visitor
from @DailyVisits where dt = DATEADD(dd, -1, d1.dt)
) a
)
from
DailyVisist_CTW d1