# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Define Individual Context &ndash; Single Person Use Case #
#
# ## Overview ##
#
# Explore the FEC data by specifying SQL predicates that identify **Individuals**, which are people identities extracted&mdash;and somewhat cleansed&mdash;from the [Individual Contributions](https://www.fec.gov/campaign-finance-data/contributions-individuals-file-description/) file.  Inidividual records (stored in the `indiv` table), are basically distinct combinations of name and address information (city, state, zipcode) that have not been aggressively deduplicated.  Thus, there will be multiple records for a real-world person if there are variants (or typos or deception) in the identifying information for contribution records.
#
# Querying by Individual can be used to target all of the `indiv` records (and associated contribution data in `indiv_contrib`) for a single person, or for a set of people to be explored collectively.  An example of the first usage will be presented here (the second will be covered in the subsequent `dc2` notebook).  One of the limitation of Querying by Individual is that it is difficult to distinguish between the contribution of distinct people identities within a result set.
#
# Note that this approach will create the following query contexts (each of which may be used in formulating specific queries for investigation or reporting):
#
# **Principal Context View**
#
# * `ctx_indiv`
#
# **Dependent Context Views**
#
# * `ctx_indiv_contrib`

# ## Notebook Setup ##
#
# ### Configure database connect info/options ###
#
# Note: database connect string can be specified on the initial `%sql` command:
#
# ```python
# database_url = "postgresql+psycopg2://user@localhost/fecdb"
# # %sql $database_url
#
# ```
#
# Or, connect string is taken from DATABASE_URL environment variable (if not specified for `%sql`):
#
# ```python
# # %sql
#
# ```

# %load_ext sql
# %config SqlMagic.autopandas=True
# %config InteractiveShell.ast_node_interactivity='last_expr_or_assign'
# connect string taken from DATABASE_URL environment variable
# %sql

# ### Clear context ###
#
# Note that we drop *all* context views so we won't have any inconsistencies after this notebook is run.  After defining `ctx_indiv` below, we will define all dependent views (see Overview, above), and leave any higher-order or orthogonal views undefined

# %sql drop view if exists ctx_dseg_memb     cascade
# %sql drop view if exists ctx_dseg          cascade
# %sql drop view if exists ctx_donor_contrib cascade
# %sql drop view if exists ctx_donor         cascade
# %sql drop view if exists ctx_household     cascade
# %sql drop view if exists ctx_iseg_memb     cascade
# %sql drop view if exists ctx_iseg          cascade
# %sql drop view if exists ctx_indiv_contrib cascade
# %sql drop view if exists ctx_indiv         cascade

# ### Set styling ###

# + language="html"
# <style>
#   tr, th, td {
#     text-align: left !important;
#   }
# </style>
# -

# ## Create Principal View (`ctx_indiv`) ##
#
# For this use case, we'll identify the `indiv` records associated with an identity that we previously queried (in `el_queries1.sql` and `el_queries3.sql`)

# + language="sql"
# create or replace view ctx_indiv as
# select *
#   from indiv
#  where name like '<NAME>%'
#    and zip_code ~ '9402[58]'
#    and name !~ 'MRS\.'
# -

# Let's take a quick look at the context we just set (for validation) before proceeding

# + language="sql"
# select id,
#        name,
#        city,
#        state,
#        zip_code,
#        elect_cycles
#   from ctx_indiv
# -

# ## Create Dependent Views ##
#
# ### Create `ctx_indiv_contrib` ###
#
# Now we'll create the context view for the contributions from the targeted "Individual" records

# + language="sql"
# create or replace view ctx_indiv_contrib as
# select ic.*
#   from ctx_indiv ix
#   join indiv_contrib ic on ic.indiv_id = ix.id
# -

# And some quick validation on the view

# + language="sql"
# select count(*)             as contribs,
#        sum(transaction_amt) as total_amt,
#        array_agg(distinct elect_cycle) as elect_cycles
#   from ctx_indiv_contrib

StarcoderdataJupyterScriptsDedupFiltered