Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 158 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

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?

share|improve this question
10  
Huh? That's on topic on Stack Overflow and Meta Stack Overflow! There should be a badge for that... – Yannis Apr 20 '12 at 20:23
1  
Ugggh.... don't use float for math like this!! – JNK Apr 20 '12 at 20:38
    
Depending on the plan you're getting, putting the contents of the where clause into the join might nudge the optimiser into a more efficient plan. Of course, I don't think you can actually get a query plan from data.so ;-) – ConcernedOfTunbridgeWells Apr 20 '12 at 20:38
    
@ConcernedOfTunbridgeWells There is an "Include execution plan" checkbox on there, actually. – jadarnel27 Apr 20 '12 at 20:54
    
@JNK: Oh really? Why not? Is there a better way? – Brian Gideon Apr 20 '12 at 20:54
    
@BrianGideon It runs for me (without timing out) with CorncernedOfTurnbridgeWells suggestion of moving the WHERE clause into the JOIN. Here is a revised version. – jadarnel27 Apr 20 '12 at 20:56
    
@jadamel27 - that's useful. I never noticed it before. However the stats I was doing mostly came from dba.se, so the volumes weren't terribly great. – ConcernedOfTunbridgeWells Apr 20 '12 at 21:07
    
@jadarnel27: Yeah, I just did the same thing and got it to work. But, now it's being really spotty again :( – Brian Gideon Apr 20 '12 at 21:09
    
Wow, this guy had to type 875 characters for each rep point. – Brian Gideon Apr 20 '12 at 21:10
    
@YahooAnswersenthusiast Ooh can we get SO rep on a question like that – Some Helpful Commenter Apr 20 '12 at 21:13
    
@BrianGideon on the opposite side this guy got more than an 100 rep points per character See how you rank here – Some Helpful Commenter Apr 20 '12 at 21:27
    
@SomeHelpfulCommenter: Ooo...that's cool. – Brian Gideon Apr 20 '12 at 23:52
up vote 0 down vote accepted

As indicated in the comments Jardanel27 forked the query and moved the WHERE into the join, as suggested by ConcernedOfTunbridgeWells

SELECT TOP 100
    u.Id AS [User Link],
    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
    AND u.Reputation > 1000
GROUP BY
    u.Id
ORDER BY
    PostLengthPerReputation DESC
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .