List of Duplicate Addresses

This query will find out addresses that used by at least 10 different parties.

SELECT acct.account_number
     , party.party_name
     , locations.address1
     , locations.address2
     , locations.city
     , locations.state
     , locations.postal_code
  FROM hz_party_sites psiteo
     , hz_cust_accounts acct
     , hz_parties party
     , (SELECT /*+ index ( loc HZ_LOCATIONS_N1 ) */ location_id 
             , loc.address1
             , loc.address2
             , loc.city
             , loc.state
             , loc.postal_code
             , parties
          FROM hz_locations loc
             , (SELECT loc.address1
                     , loc.address2
                     , loc.city
                     , loc.state
                     , COUNT(DISTINCT psite.party_id) parties
                  FROM hz_locations loc
                     , hz_party_sites psite
                 WHERE psite.location_id = loc.location_id
                 GROUP BY loc.address1
                        , loc.address2
                        , loc.city
                        , loc.state
                HAVING count(*) > 10
               ) addr
         WHERE loc.address1 = addr.address1
           AND loc.address2 = addr.address2
           AND loc.city = addr.city
           AND loc.state = addr.state
     ) locations
 WHERE psiteo.location_id = locations.location_id
   AND psiteo.party_id = party.party_id
   AND acct.party_id = party.party_id
 order by locations.postal_code
     , locations.state
     , locations.city
     , locations.address1
     , party.party_name
     , acct.account_number