I have a little problem.
Date | Unique | Returning | New
..09 | 4 | 1 | 3
..10 | 2 | 1 | 1
..11 | 1 | 1 | 0
cast(EventTime as date) as 'Date',
count(distinct UserId) + count(distinct case when UserId is null then 1 end) as 'Unique users',
0 as 'Returning users',
0 as 'New users'
DiscountId = '5F8851DD-DF77-46DC-885E-46ECA93F021C' and EventName = 'DiscountClick'
cast(EventTime as date)`
isnull(sum(case when UserId(here shoudld be count) > 1 then 1 else 0 end), 1)
isnull(sum(case when UserId(count also) = 1 then 1 else 0 end), 1)
You want aggregated user information in your results. One obvious and simple solution is to group by date and user first so as to get this information per user and date and only later group by date only.
select eventdate, count(*) as unique_users, count(case when cnt > 1 then 1 end) as returning_users, count(case when cnt = 1 then 1 end) as new_users from ( select cast(eventtime as date) as eventdate, userid, count(*) as cnt from telemetrydata where ... group by cast(eventtime as date), userid ) date_user group by eventdate;