User Time Histogram

2

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @days as int = ##Days:int?90##
declare @userorange as nvarchar(40) = ##UserOrange:string##
declare @usercyan as nvarchar(40) = ##UserCyan:string?-##
declare @userred as nvarchar(40) = ##UserRed:string?-##
declare @usergreen as nvarchar(40) = ##UserGreen:string?-##
declare @userpurple as nvarchar(40) = ##UserPurple:string?-##
declare @pivot as nvarchar(max)
declare @query as nvarchar(max)


declare @nexus as date = dateadd(day, - @days, CURRENT_TIMESTAMP)

create table #USERS (
    UserId int not null,
    Name NVarchar(40) not null,
    Reputation int not null
);

insert into #USERS
    select u.Id as UserId,
           '''' + u.DisplayName + '''' as Name,
           u.Reputation as Reputation
    from Users u
    where u.DisplayName in (@userorange, @usercyan, @userred, @usergreen, @userpurple)
      -- and not u.DisplayName in ('Cyan', 'Red', 'Green', 'Purple')
      and not exists (
        select s.Id
        from Users s
        where s.Id != u.Id
          and s.Reputation > u.Reputation
          and s.DisplayName = u.DisplayName)
;

SET @pivot = STUFF((SELECT ',' + QUOTENAME(Name) 
            FROM #USERS
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
;

with Activity as (

    select u.Name,
           DatePart(hour, CreationDate) as Hour,
           'Edits' as Action,
           count (distinct RevisionGUID) as ActCount
    from #USERS u inner join PostHistory ph on u.UserId = ph.UserId
    where ph.CreationDate >= @nexus
    group by u.Name, DatePart(hour, CreationDate)
    
  UNION ALL
  
    select u.Name,
          DatePart(hour, c.CreationDate) as Hour,
          'Comments' as Action,
          count (*) as ActCount
    from #USERS u inner join Comments c on u.UserId = c.UserId
    where c.CreationDate >= @nexus
    group by u.Name, DatePart(hour, c.CreationDate)
    )
select Hour, Name, Sum(ActCount) as Actions
into #results
from Activity
group by Name, Hour

-- select * from #results
set @query = 'select * from #results pivot (sum(Actions) for Name in (' + @pivot + ')) as Pivoted order by Hour ASC'

execute (@query)

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results