# Finding which users have the most questions with unaccepted answers

I was looking at a few profiles this afternoon and noticed a few users had plenty of open questions. This led me to wonder which users actually had the highest amount of questions with unaccepted answers (both in size and percent). So with all the hoopla I kept seeing in the 2nd monitor about SEDE queries, I decided to try my hand at writing one.

with unanswered as (
select
posts.Id as PostId
from
posts
where posts.PostTypeId = 1            -- questions
and posts.OwnerUserId is not null   -- user exists
and posts.ClosedDate is null        -- still open
group by posts.Id
),

percentages as (
select
users.Id as UserId,
count(posts.Id) as Questions,
from
users left outer join
posts on users.Id = posts.OwnerUserId
left outer join
where
posts.PostTypeId = 1
group by
users.Id
)

select top ##MaxRowsToSelect:int?100##
Questions,
from percentages


This is essentially a good, clear and effective query. The only feedback I can offer is minor and somewhat subjective.

1. In the first CTE, unanswered, I personally would use distinct rather than grouping by posts.id. I group by when I am using an aggregate function; since there's no aggregate, distinct expresses the intention clearer to me.

2. I'm not intimately familiar with SEDE but I think you can inner join between users and posts in the percentages CTE. This is likely to be more performant, and again it expresses the intention clearer. The left outer join to unanswered still makes sense because you want to include the count of answered as well as unanswered posts. (Personally, I write left join rather than left outer join - less typing, totally equivalent).

3. I'm not a huge fan of your layout and indentation style. Wholly subjective (and I don't know if I'm in the minority or you are) but I usually start each new query clause (select, from, joins, where, etc) on a new line, and leave a blank line between each clause.

4. I recommend aliasing all tables/rowsets for clarity, e.g. ...from users as U..., and referring to the alias e.g. select U.Id.... So long as you choose sensible aliases (never just A, B, C etc) this is clear and more compact.

5. Your final ordering, by UnansweredPct, doesn't guarantee the same order between re-runs (as two users could and do have the same value in that column). I'd order by more columns to ensure consistent ordering - it isn't the end of the world in this particular query, but in some cases consistency can be really important, so it's a good habit to have.

6. I am not a fan of using top in any "long term" SQL queries (in the context of my day job, any query which will be used in production rather than just ad-hoc code). Whether you consider your query to be the former or the latter is up to you ;-) My "production" approach would be to add a Row_Number() function, ordering by UnansweredPct (and other columns, see above), then restrict where RowNo <= ##MaxRowsToSelect:int?100## instead of the top. This also means you can just order by RowNo.