Last night in The 2nd Monitor there was a discussion about postless users - I wanted to see how many there are, whether they're one-timers that showed up once, registered and never came back, and whether they vote at all.
So I whipped up this query on SEDE, showing voters and their post count, as well as other information:
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
Returns 26740 rows returned in 211 ms (cached). Is there any way it could be improved, performance or otherwise?
Postless Users query on SEDE.