CODEWITHSUNDEEP
sqlsql-server
Nerf
Nerf

Reputation: 938

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)

@EDIT: Ok, two of your results work perfect. But I need now integrate it with other query. SELECT '5F8851DD-DF77-46DC-885E-46ECA93F021C', cast([dbo].[TelemetryData].[EventTime] as date) as 'Date', sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountLike' then 1 else 0 end) as 'Likes', sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountDislike' then 1 else 0 end) as 'Dis likes', sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountSharing' then 1 else 0 end) as 'Shares', SUM(case when [dbo].[TelemetryData].[EventName]='DiscountView' then 1 else 0 end) as 'Views', SUM(case when [dbo].[TelemetryData].[EventName]='DiscountClick' then 1 else 0 end) as 'Clicks', Sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountCode' then 1 else 0 end) as 'Downloaded codes', Sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountSave' then 1 else 0 end) as 'Saves', sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountClickWWW' then 1 else 0 end) as 'Page redirections', Round( cast(Sum(case when [dbo].[TelemetryData].[EventName]='DiscountClick' then 1 else 0 end) as float) / cast( case when SUM(case when [dbo].[TelemetryData].[EventName]='DiscountView' then 1 else 0 end) = 0 then 1 else SUM(case when [dbo].[TelemetryData].[EventName]='DiscountView' then 1 else 0 end) end as float) * 100, 2) as 'Average CTR', 0 as 'Unique users', 0 as 'New users', 0 as 'Returning users', Sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountCommentPositive' then 1 else 0 end) as 'Positive comments', sum(case when [dbo].[TelemetryData].[EventName] = 'DiscountCommentNegative' then 1 else 0 end) as 'Negative comments' from [dbo].[TelemetryData] where [dbo].[TelemetryData].[DiscountId] = '5F8851DD-DF77-46DC-885E-46ECA93F021C' and ([dbo].[TelemetryData].[EventName] = 'DiscountView' or [dbo].[TelemetryData].[EventName] = 'DiscountClick' or [dbo].[TelemetryData].[EventName] = 'DiscountDislike' or [dbo].[TelemetryData].[EventName] = 'DiscountCode' or [dbo].[TelemetryData].[EventName] = 'DiscountLike' or [dbo].[TelemetryData].[EventName] = 'DiscountSharing' or [dbo].[TelemetryData].[EventName] = 'DiscountClickWWW' or [dbo].[TelemetryData].[EventName] = 'DiscountSave' or [dbo].[TelemetryData].[EventName] = 'DiscountCommentPositive' or [dbo].[TelemetryData].[EventName] = 'DiscountCommentNegative') group by cast([dbo].[TelemetryData].[EventTime] as date) order by cast([dbo].[TelemetryData].[EventTime] as date) asc

Now it will be hard...

Upvotes: 1

Views: 85

Answers (5)

Kent Weigel
Kent Weigel

Reputation: 1178

The following query should work:

select EventTime,
    max(DistinctRank) [Unique],
    sum(CountOfDistinct - 1) Returning,
    max(DistinctRank) - sum(CountOfDistinct - 1) New
from
    (select distinct EventTime,
        UserId,
        rank() over (partition by EventTime order by UserId) DistinctRank,
        count(1) over (partition by EventTime, UserId) CountOfDistinct
    from TelemetryData) sub
group by EventTime

The subquery (run it separately and see for yourself) will return the unique combinations of EventTime and UserID, along with the the rank of each unique UserId for a given date, and the count of distinct values for each combination of EventTime and UserId:

EventDate               UserId                               DistinctRank  CountOfDistinct
2016-11-09 00:00:00.000 0536B088-D3DE-4C0E-903F-C2463D0AAB7E    1             1
2016-11-09 00:00:00.000 6C090D6B-9842-4CB0-9E10-F9B941C8D3A1    2             1
2016-11-09 00:00:00.000 866D70EC-93FD-4C30-BC54-C7B954F255BE    3             1
2016-11-09 00:00:00.000 FB1DD63E-F098-4191-B8F4-BEA4F9776B54    4             2
2016-11-10 00:00:00.000 NULL                                    1             1
2016-11-10 00:00:00.000 0536B088-D3DE-4C0E-903F-C2463D0AAB7E    2             2
2016-11-11 00:00:00.000 0536B088-D3DE-4C0E-903F-C2463D0AAB7E    1             2

Then the outer query gets the maximum DistinctRank for each unique pair, which is the number of unique UserIds for the EventDate, and essentially the sum of the subquery records where there were duplicates in UserId for a given EventDate, which is the number of returning users. The New column is just the difference between Unique and Returning. The result is:

Event Date                Unique  Returning  New
2016-11-09 00:00:00.000   4       1          3
2016-11-10 00:00:00.000   2       1          1
2016-11-11 00:00:00.000   1       1          0

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

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;

Upvotes: 1

Viki888
Viki888

Reputation: 2774

Try below query

select Date, uniques, returning, uniques-returning as new
from (    
    select Date, 
           sum(case when row_num = 1 then 1 else 0 end) uniques, 
           sum(case when row_num = 2 then 1 else 0 end) returning
    from(    
        select cast(EventTime as date) as Date, 
               ROW_NUMBER() over(partition by EventTime, userid order by EventTime) row_num
        from TelemetryData) cte1    
    group by Date)cte2

Hope this should help you

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7753

Try this using a Common Table Expression:

Setup

CREATE TABLE #TelemetryData
(
   EventTime  Date,
   UserId UNIQUEIDENTIFIER NULL
   )


INSERT INTO #TelemetryData
VALUES
('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')

Query

;WITH CTE
AS
(
    SELECT EventTime, 
           UserId, 
           COUNT(*) cnt, 
           ROW_NUMBER() OVER (PARTITION BY EventTime ORDER BY EventTime) RN
    FROM #TelemetryData
    GROUP BY EventTime, UserId
)

SELECT EventTime, 
       MAX(RN) AS [Unique],
       SUM(CASE WHEN cnt > 1 THEN 1 ELSE 0 END) as New, 
       SUM(CASE WHEN cnt = 1 THEN 1 ELSE 0 END) AS Returning
FROM CTE
GROUP BY EventTime

Results

EventTime   Unique  New Returning
2016-11-09  4       1   3
2016-11-10  2       1   1
2016-11-11  1       1   0

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Could be i don't understand you question but looking to your data seems that you need 

select 
     date
   , count(*) as unique
   , (count(*) - count(distinct user_id))  as returning
   ,  count(distinct user_id) as new

group by date 
were user_id is not null

Upvotes: 0

Related Questions