Optimizing a query to check if count = count of all matching items within subselect

Question

I had a query which was working correctly that looked like this:

SELECT c.id, c.uid, c.name, c.end_date FROM core.customers c
 INNER JOIN cms.fruit_policies ovp ON ovp.fruit_units = c.id
 WHERE(
   SELECT
          count(*) as c,
   FROM core.fruits f
   INNER JOIN cms.fruit_policies ov ON ov.fruit_units = f.id
   WHERE ov.template_uid IS NOT NULL
   AND ov.template_uid = 'TdBljmQIbB'
   AND f.customer_id = c.id
   group by c.id
 ) = (select count (*) from core.fruits f where f.customer_id = c.id) -- This line is where I need help
 AND ovp.template_uid <> 'TdBljmQIbB'
 AND c.end_date > '2019-04-15'
 order by c.end_date desc

The query was a little bit inefficient because I scanned the core.fruits table an extra time to do the count where all the customer_id's = c.id, I'm not sure how I'd do that within the subselect.

Answer 1

I write this code snippet blind because you did not provide much. If it is not what you are asking. Please be more specific.

WITH CUSTOMERZ AS (SELECT * FROM CORE.CUSTOMERS)
  SELECT C.ID,
     C.UID,
     C.NAME,
     C.END_DATE
FROM CUSTOMERZ C
     INNER JOIN CMS.FRUIT_POLICIES OVP
        ON OVP.FRUIT_UNITS = C.ID AND OVP.TEMPLATE_UID <> 'TdBljmQIbB'
     INNER JOIN
     (  SELECT COUNT (*) AS C, C.ID
          FROM CORE.FRUITS F
               INNER JOIN CMS.FRUIT_POLICIES OV ON OV.FRUIT_UNITS = F.ID
               INNER JOIN CUSTOMERZ C ON F.CUSTOMER_ID = C.ID
         WHERE OV.TEMPLATE_UID IS NOT NULL AND OV.TEMPLATE_UID = 'TdBljmQIbB'
      GROUP BY C.ID) QUERY1
        ON QUERY1.ID = C.ID
     INNER JOIN
     (  SELECT COUNT (*) AS C, C.ID
          FROM CORE.FRUITS F INNER JOIN CUSTOMERZ C ON F.CUSTOMER_ID = C.ID
      GROUP BY C.ID) QUERY2
        ON QUERY2.ID = C.ID
   WHERE 1 = 1 AND C.END_DATE > '2019-04-15' AND QUERY1.C = QUERY2.C
ORDER BY C.END_DATE DESC