[FIXED] How do I JOIN customers with maximum number of policies and no policies from an ERD diagram?

Issue

I want to write a query to display the name of the customer who has taken maximum number of policies and then the name of the customer who has taken no policies.

select customer.first_name
from customer
join customer_policy on customer.id = customer_policy.customer_id
having count(customer_policy.policy_id) = select max(count(policy_id)) from customer_policy

union all

select customer.first_name
from customer
full outer join customer_policy on customer.id = customer_policy.customer_id
where count(customer_policy.policy_id) = 0

I am using UNION ALL to union the two queries but any way I try to do it, I end up getting an aggregate error or a wrong answer.

What am I doing wrong?

ERD I am working with:

https://i.stack.imgur.com/67BA6.png

Solution

Use a cte that aggregates and returns the number of policies for each customer and the the max number of policies.
Finally select from the cte only the rows of the customers who have max number of policies or 0:

WITH cte AS (
  SELECT c.first_name, 
         COUNT(p.policy_id) number_of_policies,
         MAX(COUNT(p.policy_id)) OVER () max_policies
  FROM customer c LEFT JOIN customer_policy p
  ON p.customer_id = c.id
  GROUP BY c.id, c.first_name
)
SELECT first_name, number_of_policies
FROM cte
WHERE number_of_policies IN (0, max_policies)
ORDER BY number_of_policies DESC;

Answered By - forpas

Answer Checked By - Clifford M. (PHPFixing Volunteer)