{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filer and Filing ID exploration\n",
    "\n",
    "A set of custom database tables and SQL commands to explore Filer IDs in the CAL-ACCESS database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Filing ID value table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Every unique filing_id in the CAL-ACCESS database. Includes the table and column in which the unique value was observed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "INSERT INTO calaccess_processed_filingidvalue (table_name, value, occur_count)\n",
    "SELECT \n",
    "    'CVR2_CAMPAIGN_DISCLOSURE_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_CAMPAIGN_DISCLOSURE_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR2_LOBBY_DISCLOSURE_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_LOBBY_DISCLOSURE_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR2_REGISTRATION_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_REGISTRATION_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR2_SO_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_SO_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR3_VERIFICATION_INFO_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR3_VERIFICATION_INFO_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_CAMPAIGN_DISCLOSURE_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_E530_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_E530_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_F470_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_F470_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_LOBBY_DISCLOSURE_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_LOBBY_DISCLOSURE_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_REGISTRATION_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_REGISTRATION_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_SO_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_SO_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'DEBT_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"DEBT_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'EXPN_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"EXPN_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'F495P2_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"F495P2_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'F501_502_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"F501_502_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'F690P2_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"F690P2_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'HDR_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"HDR_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LATT_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LATT_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LCCM_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LCCM_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LEMP_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LEMP_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LEXP_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LEXP_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOAN_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOAN_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOBBYIST_FIRM_EMPLOYER1_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM_EMPLOYER1_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOBBYIST_FIRM_EMPLOYER2_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM_EMPLOYER2_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOBBY_AMENDMENTS_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBY_AMENDMENTS_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOTH_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOTH_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LPAY_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LPAY_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'RCPT_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"RCPT_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'RECEIVED_FILINGS_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"RECEIVED_FILINGS_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'S401_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S401_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'S496_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S496_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'S497_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S497_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'S498_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S498_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'SMRY_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"SMRY_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'SPLT_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"SPLT_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'TEXT_MEMO_CD' as table_name,\n",
    "    \"FILING_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"TEXT_MEMO_CD\"\n",
    "WHERE \"FILING_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Filer ID Value table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Every unique filer_id in the CAL-ACCESS database. Includes the table and column in which the unique value was observed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "INSERT INTO calaccess_processed_fileridvalue (table_name, column_name, value, occur_count)\n",
    "SELECT \n",
    "    'BALLOT_MEASURES_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"BALLOT_MEASURES_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\" as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"CVR_CAMPAIGN_DISCLOSURE_CD\"\n",
    "WHERE \"FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_F470_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\" as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"CVR_F470_CD\"\n",
    "WHERE \"FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_LOBBY_DISCLOSURE_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\" as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"CVR_LOBBY_DISCLOSURE_CD\"\n",
    "WHERE \"FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_REGISTRATION_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\" as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"CVR_REGISTRATION_CD\"\n",
    "WHERE \"FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'CVR_SO_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\" as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"CVR_SO_CD\"\n",
    "WHERE \"FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'EFS_FILING_LOG_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\" as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"EFS_FILING_LOG_CD\"\n",
    "WHERE \"FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'F501_502_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\" as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"F501_502_CD\"\n",
    "WHERE \"FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILERNAME_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\"::varchar as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"FILERNAME_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILERS_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\"::varchar as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"FILERS_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_ACRONYMS_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\"::varchar as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"FILER_ACRONYMS_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_ADDRESS_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\"::varchar as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"FILER_ADDRESS_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_ETHICS_CLASS_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\"::varchar as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"FILER_ETHICS_CLASS_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_FILINGS_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\"::varchar as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"FILER_FILINGS_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_INTERESTS_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"FILER_INTERESTS_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_TO_FILER_TYPE_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"FILER_TO_FILER_TYPE_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_XREF_CD' as table_name,\n",
    "     'FILER_ID' as column_name,\n",
    "     \"FILER_ID\"::varchar as value,\n",
    "     COUNT(*) occur_count\n",
    "FROM \"FILER_XREF_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOBBYING_CHG_LOG_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"LOBBYING_CHG_LOG_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOBBYIST_CONTRIBUTIONS1_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"LOBBYIST_CONTRIBUTIONS1_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOBBYIST_CONTRIBUTIONS2_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"LOBBYIST_CONTRIBUTIONS2_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'LOBBYIST_CONTRIBUTIONS3_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"LOBBYIST_CONTRIBUTIONS3_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'RECEIVED_FILINGS_CD' as table_name,\n",
    "    'FILER_ID' as column_name,\n",
    "    \"FILER_ID\"::varchar as value,\n",
    "    COUNT(*) occur_count\n",
    "FROM \"RECEIVED_FILINGS_CD\"\n",
    "WHERE \"FILER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- xref_ids\n",
    "SELECT \n",
    "    'FILERNAME_CD' as table_name,\n",
    "    'XREF_FILER_ID' as column_name,\n",
    "    \"XREF_FILER_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"FILERNAME_CD\"\n",
    "WHERE \"XREF_FILER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'FILER_XREF_CD' as table_name,\n",
    "    'XREF_ID' as column_name,\n",
    "    'XREF_ID' as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"FILER_XREF_CD\"\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- ballot initiative (FILER_TYPE = 19) filer_ids?\n",
    "SELECT \n",
    "    'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name,\n",
    "    'BAL_ID' as column_name,\n",
    "    \"BAL_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_CAMPAIGN_DISCLOSURE_CD\"\n",
    "WHERE \"BAL_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT \n",
    "    'S497_CD' as table_name, \n",
    "    'BAL_ID' as column_name,\n",
    "    \"BAL_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S497_CD\"\n",
    "WHERE \"BAL_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- candidate/officeholder (FILER_TYPE = 8) filer_ids?\n",
    "SELECT\n",
    "    'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name ,\n",
    "    'CAND_ID' as column_name,\n",
    "    \"CAND_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_CAMPAIGN_DISCLOSURE_CD\"\n",
    "WHERE \"CAND_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'S497_CD' as table_name,\n",
    "    'CAND_ID' as column_name,\n",
    "    \"CAND_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S497_CD\"\n",
    "WHERE \"CAND_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- client (FILER_TYPE = 1) filer_ids?\n",
    "SELECT \n",
    "    'LEMP_CD' as table_name,\n",
    "    'CLIENT_ID' as column_name,\n",
    "    \"CLIENT_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LEMP_CD\"\n",
    "WHERE \"CLIENT_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- committees includes...what?\n",
    "-- recipient committee and (FILER_TYPE = 16) and\n",
    "-- major donor/independent expenditure committee (FILER_TYPE = 10)?\n",
    "-- maybe others?\n",
    "SELECT\n",
    "    'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name,\n",
    "    'CMTTE_ID' as column_name,\n",
    "    \"CMTTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_CAMPAIGN_DISCLOSURE_CD\"\n",
    "WHERE \"CMTTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'CVR2_CAMPAIGN_DISCLOSURE_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_CAMPAIGN_DISCLOSURE_CD\"\n",
    "WHERE \"CMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'CVR2_SO_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_SO_CD\"\n",
    "WHERE \"CMTE_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'DEBT_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"DEBT_CD\"\n",
    "WHERE \"CMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'EXPN_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"EXPN_CD\"\n",
    "WHERE \"CMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'F501_502_CD' as table_name,\n",
    "    'COMMITTEE_ID' as column_name,\n",
    "    \"COMMITTEE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"F501_502_CD\"\n",
    "WHERE \"COMMITTEE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOAN_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOAN_CD\"\n",
    "WHERE \"CMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'RCPT_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"RCPT_CD\"\n",
    "WHERE \"CMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'S497_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S497_CD\"\n",
    "WHERE \"CMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'S498_CD' as table_name,\n",
    "    'CMTE_ID' as column_name,\n",
    "    \"CMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"S498_CD\"\n",
    "WHERE \"CMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'CVR_SO_CD' as table_name,\n",
    "    'COM82013ID' as column_name,\n",
    "    \"COM82013ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_SO_CD\"\n",
    "WHERE \"COM82013ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'RCPT_CD' as table_name,\n",
    "    'INTR_CMTEID' as column_name,\n",
    "    \"INTR_CMTEID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"RCPT_CD\"\n",
    "WHERE \"INTR_CMTEID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- contributors includes...what?\n",
    "-- individual (FILER_TYPE = 101), payment to influence (FILER_TYPE = 5)\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER1_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER1_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER2_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER2_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER3_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER3_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER_HISTORY_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER_HISTORY_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM1_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM1_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM2_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM2_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM3_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM3_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_HISTORY_CD' as table_name,\n",
    "    'CONTRIBUTOR_ID' as column_name,\n",
    "    \"CONTRIBUTOR_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM_HISTORY_CD\"\n",
    "WHERE \"CONTRIBUTOR_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- employers (FILER_TYPE 2)?\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER1_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER1_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER2_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER2_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER3_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER3_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER_FIRMS1_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER_FIRMS1_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER_FIRMS2_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER_FIRMS2_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER_HISTORY_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMPLOYER_HISTORY_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMP_LOBBYIST1_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMP_LOBBYIST1_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMP_LOBBYIST2_CD' as table_name,\n",
    "    'EMPLOYER_ID' as column_name,\n",
    "    \"EMPLOYER_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMP_LOBBYIST2_CD\"\n",
    "WHERE \"EMPLOYER_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LPAY_CD' as table_name,\n",
    "    'EMPLR_ID' as column_name,\n",
    "    \"EMPLR_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LPAY_CD\"\n",
    "WHERE \"EMPLR_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- firms (FILER_TYPE 8) filer_ids?\n",
    "SELECT\n",
    "    'CVR_LOBBY_DISCLOSURE_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_LOBBY_DISCLOSURE_CD\"\n",
    "WHERE \"FIRM_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LEMP_CD' as table_name,\n",
    "    'SUBFIRM_ID' as column_name,\n",
    "    \"SUBFIRM_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LEMP_CD\"\n",
    "WHERE \"SUBFIRM_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER_FIRMS1_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_EMPLOYER_FIRMS1_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMPLOYER_FIRMS2_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_EMPLOYER_FIRMS2_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM1_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM1_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM2_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM2_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM3_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM3_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_EMPLOYER1_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM_EMPLOYER1_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_EMPLOYER2_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM_EMPLOYER2_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_HISTORY_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"LOBBYIST_FIRM_HISTORY_CD\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM_HISTORY_CD\"\n",
    "WHERE \"LOBBYIST_FIRM_HISTORY_CD\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_LOBBYIST1_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM_LOBBYIST1_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_LOBBYIST2_CD' as table_name,\n",
    "    'FIRM_ID' as column_name,\n",
    "    \"FIRM_ID\"::varchar as value,\n",
    "    COUNT(*)\n",
    "FROM \"LOBBYIST_FIRM_LOBBYIST2_CD\"\n",
    "WHERE \"FIRM_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- lobbyists (FILER_TYPE 4) filer_ids?\n",
    "SELECT\n",
    "    'LOBBYIST_EMP_LOBBYIST1_CD' as table_name,\n",
    "    'LOBBYIST_ID' as column_name,\n",
    "    \"LOBBYIST_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMP_LOBBYIST1_CD\"\n",
    "WHERE \"LOBBYIST_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_EMP_LOBBYIST2_CD' as table_name,\n",
    "    'LOBBYIST_ID' as column_name,\n",
    "    \"LOBBYIST_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_EMP_LOBBYIST2_CD\"\n",
    "WHERE \"LOBBYIST_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_LOBBYIST1_CD' as table_name,\n",
    "    'LOBBYIST_ID' as column_name,\n",
    "    \"LOBBYIST_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM_LOBBYIST1_CD\"\n",
    "WHERE \"LOBBYIST_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_FIRM_LOBBYIST2_CD' as table_name,\n",
    "    'LOBBYIST_ID' as column_name,\n",
    "    \"LOBBYIST_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_FIRM_LOBBYIST2_CD\"\n",
    "WHERE \"LOBBYIST_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- candidate/officeholder (FILER_TYPE 8)\n",
    "SELECT\n",
    "    'CVR_LOBBY_DISCLOSURE_CD' as table_name,\n",
    "    'RCPCMTE_ID' as column_name,\n",
    "    \"RCPCMTE_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_LOBBY_DISCLOSURE_CD\"\n",
    "WHERE \"RCPCMTE_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LCCM_CD' as table_name,\n",
    "    'RECIP_ID' as column_name,\n",
    "    \"RECIP_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LCCM_CD\"\n",
    "WHERE \"RECIP_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_CONTRIBUTIONS1_CD' as table_name,\n",
    "    'RECIPIENT_ID' as column_name,\n",
    "    \"RECIPIENT_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_CONTRIBUTIONS1_CD\"\n",
    "WHERE \"RECIPIENT_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_CONTRIBUTIONS2_CD' as table_name,\n",
    "    'RECIPIENT_ID' as column_name,\n",
    "    \"RECIPIENT_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_CONTRIBUTIONS2_CD\"\n",
    "WHERE \"RECIPIENT_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYIST_CONTRIBUTIONS3_CD' as table_name,\n",
    "    'RECIPIENT_ID' as column_name,\n",
    "    \"RECIPIENT_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYIST_CONTRIBUTIONS3_CD\"\n",
    "WHERE \"RECIPIENT_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "-- ????\n",
    "SELECT\n",
    "    'CVR2_LOBBY_DISCLOSURE_CD' as table_name,\n",
    "    'ENTITY_ID' as column_name,\n",
    "    \"ENTITY_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_LOBBY_DISCLOSURE_CD\"\n",
    "WHERE \"ENTITY_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'CVR2_REGISTRATION_CD' as table_name,\n",
    "    'ENTITY_ID' as column_name,\n",
    "    \"ENTITY_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR2_REGISTRATION_CD\"\n",
    "WHERE \"ENTITY_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'CVR_LOBBY_DISCLOSURE_CD' as table_name,\n",
    "    'SENDER_ID' as column_name,\n",
    "    \"SENDER_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_LOBBY_DISCLOSURE_CD\"\n",
    "WHERE \"SENDER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'CVR_REGISTRATION_CD' as table_name,\n",
    "    'SENDER_ID' as column_name,\n",
    "    \"SENDER_ID\" as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"CVR_REGISTRATION_CD\"\n",
    "WHERE \"SENDER_ID\" <> ''\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'LOBBYING_CHG_LOG_CD' as table_name,\n",
    "    'ENTITY_ID' as column_name,\n",
    "    \"ENTITY_ID\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"LOBBYING_CHG_LOG_CD\"\n",
    "WHERE \"ENTITY_ID\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'FILER_LINKS_CD' as table_name,\n",
    "    'FILER_ID_A' as column_name,\n",
    "    \"FILER_ID_A\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"FILER_LINKS_CD\"\n",
    "WHERE \"FILER_ID_A\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3\n",
    "UNION ALL\n",
    "SELECT\n",
    "    'FILER_LINKS_CD' as table_name,\n",
    "    'FILER_ID_B' as column_name,\n",
    "    \"FILER_ID_B\"::varchar as value,\n",
    "    COUNT(*) as occur_count\n",
    "FROM \"FILER_LINKS_CD\"\n",
    "WHERE \"FILER_ID_B\" IS NOT NULL\n",
    "GROUP BY 1, 2, 3;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}