6

I've found a query that returns questions from users with a certain minimum reputation:

select top 20 p.id as [Post Link], p.score, p.viewcount, p.answercount, p.tags,
  u.reputation
from posts p, users u 
where p.owneruserid = u.id
  and u.reputation >= 1000
  and p.deletiondate is null
  and p.tags like '%python%'
order by p.creationdate desc

but I'd like to further filter the resultset by users who have answered more questions than they have asked - or alternatively, have a minimum reputation from answers only. Is this possible?

  • 7
    Anything is possible given enough time, money and white chocolate. – user102937 Dec 1 at 23:24
  • I like your attitude :-D – thebjorn Dec 1 at 23:24
  • 1
    Here's a starting point for you: data.stackexchange.com/stackoverflow/query/1160991 ... you now only need to join the users DB to filter the reputation or to get a handy link to each user profile. – Tom Dec 2 at 0:22
  • 3
    @RobertHarvey You forgot duct tape. – connectyourcharger Dec 2 at 0:24
  • @connectyourcharger: I'll use that one next time (I rotate the third term around. Last time I think it was donuts). – user102937 Dec 2 at 0:26
  • @connectyourcharger FYI, if you're interested in duct tape, you may wish to check out the "Red Green Show", e.g., as described in The star of 'The Red Green Show' says farewell with duct tape firmly in hand. – John Omielan Dec 2 at 0:35
  • @JohnOmielan T'was more of a joke, but thanks for showing me that gem! – connectyourcharger Dec 2 at 1:16
6

Sure, here you go. I'm not sure if this is the most performant way, but I've added some code to calculate the number of answers and questions with the cross apply and outer apply operators:

cross apply (
  select count(*) as value
    from posts
    where owneruserid = u.id
      and posttypeid = 2
) answers
cross apply (
  select count(*) as value
    from posts
    where owneruserid = u.id
      and posttypeid = 1
) questions

so that I can compare them later:

and answers.value > questions.value

(Oh, I also removed the filter on DeletionDate, since deleted posts never show up in that table.)

  • 1
    I expect a two column projection with sum(case when posttypeid = 1 then 1 else 0 end) Q, sum(case when posttypeid = 2 then 1 else 0 end) A to perform better. – rene Dec 2 at 7:01
  • 2
    Probably, but this query times out and I detest having to specify the projected columns twice (why doesn't SQL understand GROUP BY p.*, u.*?) – Glorfindel Dec 2 at 7:53
  • maybe inner join on posttags and tags to get those python tags? That should get rid of a full table scan over posts(withdeleted) – rene Dec 2 at 8:04
  • 1
    Sure, but my original query doesn't need it... I went for the option that took the least amount of effort to type and so far I'm happy with it :) – Glorfindel Dec 2 at 8:06
  • Does the cross apply work for users that have either no answers or questions? – dfhwze Dec 2 at 9:44
  • 1
    @dfhwze it should; select count(*) would then return 0. – Glorfindel Dec 2 at 9:45

You must log in to answer this question.

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