How to display an individual with highest number of posts and an individual with lowest number of posts at the same time in SQL Server?

Below is the table I have created and I have also inserted values in it:

CREATE TABLE user_posts   
(username varchar(25),   
num_of_posts int)  
GO  
  
INSERT INTO user_posts VALUES ('Mark' , 23),  
               ('Donald' , 23),  
               ('George' , 7),  
               ('Edward' , 2),  
               ('Richard' , 15),  
               ('Michael' , 1),  
               ('John' , 1),  
               ('Paul' , 12),  
               ('Daniel' , 9)  
GO

Now I am trying to solve the following question:

Write a query that displays the team members who have published the highest and lowest number of posts.

This is what I have done:

SELECT A.[Team members with highest number of reports], B.[Team members with lowest number of reports]
FROM
(SELECT username AS 'Team members with highest number of reports'
FROM user_posts 
WHERE num_of_posts = (SELECT MAX(num_of_posts) FROM user_posts)
) A,
(SELECT username AS 'Team members with lowest number of reports'
FROM user_posts 
WHERE num_of_posts = (SELECT MIN(num_of_posts) FROM user_posts)
) B

I received the following result:

I almost got my desired result but the only problem is that it is showing duplicate values. I even tried adding DISTINCT for both username and num_of_posts, and still it is showing duplicate values. I am still not able to figure out why my query logic is returning duplicate values.

How can this issue be fixed ?

Answer

You’re doing a cartesian join between both lists, but there is no correlation between them so you can’t really join them – what if you had 3 users sharing max posts and 1 with min posts – which of the 3 rows matches up to the 1 row?

You probably want something like the following which effectively gives boths separate lists in a single “union”:

select username, 
  case when num_of_posts=minp then 
     Concat('Min number ', minp) else 
     Concat('Max number ',maxp) 
     end Postcount
from (
    select *, 
      Max(num_of_posts) over() maxp, 
      Min(num_of_posts) over() minp
    from user_posts
)p
where num_of_posts in (minp,maxp);

You could also tweak it slightly to return just a row for the min and max counts and the users for each:

select String_Agg(username, ',') UserName, 
    Concat(Iif(num_of_posts=Max(maxp),'Max posts - ','Min posts - '),num_of_posts) PostCount
from (
    select *, 
      Max(num_of_posts) over() maxp, 
      Min(num_of_posts) over() minp
    from user_posts
)p
where num_of_posts in (minp,maxp)
group by num_of_posts;

Which gives:

One other option is to full join between each separate query results using row_number to artificially create a join condition:

with maxp as (
    SELECT username AS [Team members with highest number of reports], 
      Row_Number() over(order by (select null)) rn
    FROM user_posts 
    WHERE num_of_posts = (SELECT MAX(num_of_posts) FROM user_posts)
), minp as (
    SELECT username AS [Team members with lowest number of reports],
      Row_Number() over(order by (select null)) rn
    FROM user_posts 
    WHERE num_of_posts = (SELECT MIN(num_of_posts) FROM user_posts)
)
select [Team members with highest number of reports], 
       [Team members with lowest number of reports] 
from maxp full join minp on maxp.rn = minp.rn;