I have the following Data Explorer query which attempts to compute the number of characters per reputation sorted in descending order for each user. Think of it as a silly level of effort metric. Who typed the most number of characters for each reputation point earned? Just a fun little comparison.
select top 10 /* 100 would be better */
u.Id,
u.DisplayName,
min(u.Reputation) as Reputation,
sum(len(p.Body)) as TotalLength,
count(*) as TotalPosts,
avg(len(p.Body)) as AvgPostLength,
(cast(sum(len(p.Body)) as float) / cast(min(u.Reputation) as float)) as PostLengthPerReputation
from
Posts p
inner join Users u ON u.Id = p.OwnerUserId
where
u.Reputation > 100000 /* 1000 would be better */
group by
u.Id, u.DisplayName
order by
PostLengthPerReputation desc
The problem is that I would like to do a top 10 (or even 100) for users above say 10,000 or even 1,000 reputation, but the query will timeout. I did get it to return for users with reputation > 100,000, but that is not very interesting. Anyone have any ideas on how to get this to work?