0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with sub as (

  select distinct
    Users.Id
   ,Users.DisplayName
   ,Users.CreationDate
   ,Users.LastAccessDate
   ,datediff(day, cast(Users.CreationDate as date), cast(Users.LastAccessDate as date)) MembershipDays
   ,case when cast(Users.CreationDate as date) = cast(Users.LastAccessDate as date) then 1 else 0 end IsOneTimer
   ,sum(case when Posts.Id is null then 0 else 1 end) over (partition by Users.Id) PostCount
   ,Users.Reputation
   ,Users.UpVotes + Users.DownVotes TotalVotes
   ,Users.UpVotes
   ,Users.DownVotes
  from Users
  left join Posts on Users.Id = Posts.OwnerUserId
  where Users.Id > 0

), agg as (

  select
     Id
    ,DisplayName
    ,CreationDate
    ,LastAccessDate
    ,IsOneTimer
    ,MembershipDays
    ,case when MembershipDays = 0 then TotalVotes else TotalVotes / cast(MembershipDays as decimal) end VotesPerDay
    ,PostCount
    ,Reputation
    ,TotalVotes
    ,UpVotes
    ,DownVotes
  from sub
  where TotalVotes > 0)

select
     Id
    ,DisplayName
    ,CreationDate
    ,LastAccessDate
    ,IsOneTimer
    ,MembershipDays
    ,round(VotesPerDay, 3) VotesPerDay
    ,PostCount
    ,Reputation
    ,TotalVotes
    ,UpVotes
    ,DownVotes
from agg
order by
  PostCount
 ,cast(VotesPerDay as int) desc
 ,cast(LastAccessDate as date) desc
 ,TotalVotes desc
 ,Reputation
 ,IsOneTimer
 ,CreationDate

fork query

edited nov 17 16

created nov 17 16 retailcoder