6

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?

Improve this question
12
  • 10
    Huh? That's on topic on Stack Overflow and Meta Stack Overflow! There should be a badge for that...
    – yannis
    Apr 20, 2012 at 20:23
  • 2
    Ugggh.... don't use float for math like this!!
    – JNK
    Apr 20, 2012 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, 2012 at 20:38
  • @ConcernedOfTunbridgeWells There is an "Include execution plan" checkbox on there, actually.
    – Josh Darnell
    Apr 20, 2012 at 20:54
  • @JNK: Oh really? Why not? Is there a better way?
    – Brian Gideon
    Apr 20, 2012 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.
    – Josh Darnell
    Apr 20, 2012 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, 2012 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, 2012 at 21:09
  • Wow, this guy had to type 875 characters for each rep point.
    – Brian Gideon
    Apr 20, 2012 at 21:10
  • @YahooAnswersenthusiast Ooh can we get SO rep on a question like that
    – Some Helpful Commenter
    Apr 20, 2012 at 21:13
  • 1
    @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, 2012 at 21:27
  • @SomeHelpfulCommenter: Ooo...that's cool.
    – Brian Gideon
    Apr 20, 2012 at 23:52

1 Answer 1

Reset to default
0

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
Improve this answer

You must log in to answer this question.

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