-- Obviously you'll want to change 'm_pioneer', and if you want to port this to other ILSes,
-- you'll want to change l_user_addr1_zip, l_user_city_state, l_user_addr1_county, etc.



CREATE TABLE m_pioneer.zips (
  zipcode TEXT,
  city_state TEXT,
  county TEXT,
  num INT
);

CREATE OR REPLACE FUNCTION analyze_zips() RETURNS VOID AS $$

DECLARE
  zip TEXT;
  
BEGIN

FOR zip IN 
  SELECT DISTINCT SUBSTRING(l_user_addr1_zip FROM 1 FOR 5)
  FROM m_pioneer.actor_usr_legacy
  WHERE l_user_addr1_zip <> ''
  LOOP
    INSERT INTO m_pioneer.zips (zipcode, city_state, county, num) 
      SELECT zip, l_user_addr1_city_state, l_user_addr1_county, count(*)
        FROM m_pioneer.actor_usr_legacy 
        WHERE l_user_addr1_zip=zip 
        GROUP BY 1,2,3;
  END LOOP;

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION report_zips() RETURNS SETOF m_pioneer.zips AS $$

DECLARE
  zip TEXT;
  output m_pioneer.zips%ROWTYPE;  

BEGIN

FOR zip IN 
  SELECT DISTINCT zipcode
    FROM m_pioneer.zips
  LOOP
SELECT INTO OUTPUT *
  FROM m_pioneer.zips
  WHERE num = (	SELECT MAX(num) FROM m_pioneer.zips WHERE zipcode=zip ) AND zipcode=zip
  LIMIT 1;
  RETURN NEXT output;
END LOOP;

END;

$$ LANGUAGE plpgsql;

SELECT analyze_zips();

SELECT * FROM report_zips() ORDER BY num DESC;

