Nerf Nerf - 14 days ago 8
SQL Question

Count grouped colums and group them via other column

I have a little problem.
The data:


2016-11-09 0536B088-D3DE-4C0E-903F-C2463D0AAB7E
2016-11-09 866D70EC-93FD-4C30-BC54-C7B954F255BE
2016-11-09 6C090D6B-9842-4CB0-9E10-F9B941C8D3A1
2016-11-09 FB1DD63E-F098-4191-B8F4-BEA4F9776B54
2016-11-09 FB1DD63E-F098-4191-B8F4-BEA4F9776B54
2016-11-10 0536B088-D3DE-4C0E-903F-C2463D0AAB7E
2016-11-10 NULL
2016-11-10 0536B088-D3DE-4C0E-903F-C2463D0AAB7E
2016-11-11 0536B088-D3DE-4C0E-903F-C2463D0AAB7E
2016-11-11 0536B088-D3DE-4C0E-903F-C2463D0AAB7E


From it I want to count UserId and group via Date.
I should be like this:


Date | Unique | Returning | New
..09 | 4 | 1 | 3
..10 | 2 | 1 | 1
..11 | 1 | 1 | 0


How I can do it?
I have this query.

select
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'
from
TelemetryData
where
DiscountId = '5F8851DD-DF77-46DC-885E-46ECA93F021C' and EventName = 'DiscountClick'
group by
cast(EventTime as date)`


Unique users = unique with NULL too!

Returing users = UserId who clicked more than 1 times
isnull(sum(case when UserId(here shoudld be count) > 1 then 1 else 0 end), 1)


New users who clicked only one!
isnull(sum(case when UserId(count also) = 1 then 1 else 0 end), 1)

Answer

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;
Comments