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?

Improve this question
4

1 Answer 1

Reset to default
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.)

Improve this answer
6
  • 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, 2019 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 Mod
    Dec 2, 2019 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, 2019 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 Mod
    Dec 2, 2019 at 8:06
  • Does the cross apply work for users that have either no answers or questions?
    – dfhwze
    Dec 2, 2019 at 9:44
  • 1
    @dfhwze it should; select count(*) would then return 0.
    – Glorfindel Mod
    Dec 2, 2019 at 9:45

You must log in to answer this question.

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