NYCDB query to help compare HPD-registered corporation addresses vs. head officer addresses

nycdb-analyze-hpd-corps-vs-head-officers.sql

CREATE FUNCTION pg_temp.address(hpd_contacts) RETURNS TEXT AS 
$$
    SELECT
        CASE WHEN $1.businessstreetname IS NULL THEN
            ''::text
        ELSE
            COALESCE($1.businesshousenumber, '') || ' ' ||
            $1.businessstreetname ||
            CASE WHEN $1.businessapartment IS NULL THEN
                ''
            ELSE
                ' #' || $1.businessapartment
            END || ' / ' ||
            COALESCE($1.businesscity, '') || ' ' ||
            COALESCE($1.businessstate, '') || ' ' ||
            COALESCE($1.businesszip, '')
        END
$$ language sql;

SELECT
    company.registrationid,
    company.corporationname,
    COALESCE(head_officer.firstname, '') || ' ' || COALESCE(head_officer.lastname) AS head_officer,
    pg_temp.address(company) AS company_address,
    pg_temp.address(head_officer) AS head_officer_address
FROM
    hpd_contacts AS company
LEFT JOIN
    hpd_contacts AS head_officer
    ON company.registrationid = head_officer.registrationid
WHERE
    company.corporationname IS NOT NULL AND
    head_officer.firstname IS NOT NULL AND
    company.type = 'CorporateOwner' AND
    head_officer.type = 'HeadOfficer' AND
    pg_temp.address(company) != pg_temp.address(head_officer)
LIMIT 100;