# This code merges data on nonanswers and competition

#(.packages())
#rm(list = ls())

library(RPostgreSQL)
library(dplyr, warn.conflicts = FALSE)

pg <- dbConnect(PostgreSQL())
dbExecute(pg, "SET work_mem TO '32GB'") # Speeds-up creation of temporary tables

# Restrict to all_ans >= all_ans_min
# 5 is 25th percentile for CEOs
all_ans_min <- 5

# Define PG data ----

# Project schema
rs <- dbGetQuery(pg, "SET search_path TO nonans_product, public")
se_link <- tbl(pg, "se_link")
crsp_calls <- tbl(pg, "crsp_calls")
fundann <- tbl(pg, "fundann")
fundqtr <- tbl(pg, "fundqtr")
nonans_rate <- tbl(pg, "nonans_rate")
comp_rate <- tbl(pg, "comp_rate")
ibes_mb <- tbl(pg, "ibes_mb")
ciq_offerings <- tbl(pg, "ciq_offerings")
sp1500 <- tbl(pg, "sp1500")
rscript <- tbl(pg, "rscript")

# Outside of project schema
executives <- tbl(pg, sql("SELECT * FROM exec_comp_2016.executives"))
equity_calc <- tbl(pg, sql("SELECT * FROM exec_comp_2016.equity_calc"))
hhi <- tbl(pg, sql("SELECT * FROM hoberg_phillips.tnic3_hhi"))
tnic3 <- tbl(pg, sql("SELECT * FROM hoberg_phillips.tnic3"))
fluid <- tbl(pg, sql("SELECT * FROM hoberg_phillips.fluidity"))
sic87dd_comtrade <- tbl(pg, sql("SELECT * FROM trade.sic87dd_comtrade"))

# Compute imports from China for sic3 and sic4 ----

# sic87dd_comtrade is summarized by {sic87dd, is_usa}
sic87dd_comtrade_alt <- 
    sic87dd_comtrade %>%
    # Extract 3-digit and 4-digit SIC
    mutate(sic4 = sql("LPAD(sic87dd::text, 4, '0')")) %>%
    mutate(sic3 = sql("substring(sic4 from 1 for 3)")) %>%
    select(year, is_usa, sic4, sic3, import_mm) %>%
    distinct() %>%
    compute()

# 1-/3-year growth in import for 3-digit SIC    
import_sic3 <- 
    sic87dd_comtrade_alt %>%
    select(-sic4) %>%
    # Aggregate by SIC3 
    group_by(year, is_usa, sic3) %>%
    summarize(import_mm = sum(import_mm)) %>%
    ungroup() %>%
    group_by(is_usa, sic3) %>%
    arrange(year) %>%
    # Compute growth rates as in Stephen Terry's JMP
    # WARNING: cannot send . and ._lag to sql
    # 1-year growth
    mutate(impgr1 = if_else(import_mm == 0 & sql(lag(import_mm)) == 0, 0, 
                            2*(import_mm - sql(lag(import_mm)))/
                                (sql(NULLIF(abs(1.0*import_mm) + lag(abs(1.0*import_mm)), 0))))) %>%
    # 3-year growth
    mutate(impgr3 = if_else(import_mm == 0 & sql(lag(import_mm, 3)) == 0, 0, 
                            2*(import_mm - sql(lag(import_mm, 3)))/
                                (sql(NULLIF(abs(1.0*import_mm) + lag(abs(1.0*import_mm), 3), 0))))) %>%
    # Compute lagged growth
    group_by(sic3, is_usa) %>%
    arrange(year) %>%
    mutate_at(.vars = vars(impgr1, impgr3), 
              .funs = funs(lag = lag(.))) %>%
    ungroup() %>%
    compute(index = list(c("year", "sic3")))

# 1-/3-year growth in import for 4-digit SIC
import_sic4 <- 
    sic87dd_comtrade_alt %>%
    select(-sic3) %>%
    # WARNING: sic87dd_comtrade_alt is already aggregated by {year, is_usa, sic87dd}
    group_by(is_usa, sic4) %>%
    arrange(year) %>%
    # Compute growth rates as in Stephen Terry's JMP
    # WARNING: cannot send . and ._lag to sql
    # 1-year growth
    mutate(impgr1 = if_else(import_mm == 0 & sql(lag(import_mm)) == 0, 0, 
                            2*(import_mm - sql(lag(import_mm)))/
                                (sql(NULLIF(abs(1.0*import_mm) + lag(abs(1.0*import_mm)), 0))))) %>%
    # 3-year growth
    mutate(impgr3 = if_else(import_mm == 0 & sql(lag(import_mm, 3)) == 0, 0, 
                            2*(import_mm - sql(lag(import_mm, 3)))/
                                (sql(NULLIF(abs(1.0*import_mm) + lag(abs(1.0*import_mm), 3), 0))))) %>%
    # Compute lagged growth
    group_by(sic4, is_usa) %>%
    arrange(year) %>%
    mutate_at(.vars = vars(impgr1, impgr3), 
              .funs = funs(lag = lag(.))) %>%
    ungroup() %>%
    compute(index = list(c("year", "sic4")))

# Compute competition rates self-/and by "industry" ----

# Get comp_rate by call
comp_rate_alt <-
    se_link %>%
    select(file_name, last_update, start_date, gvkey, fyear) %>%
    left_join(comp_rate) %>%
    select(-all_utter, -comp) %>%
    rename(comp = comp_rate) %>%
    distinct() %>%
    compute(index = list("file_name", "last_update"))

# Industry competition based on (start_date-3 months; start_date] calls in 

# Industry competition by text-based similarity (similarity score weighted) based
# on OTHER firms in an "industry" ("_i" = excluding firm i)
# NOTE: Fluidity does not have "weights" because it is a global measure, i.e., 
# aggregate change accross all firms

# WARNING: allow merge on {fyear, fyear - 1, fyear + 1}
comp_oth <- 
    union(comp_rate_alt %>%
              select(fyear,
                     gvkey_oth = gvkey, 
                     comp_oth = comp, 
                     file_name_oth = file_name, 
                     last_update_oth = last_update, 
                     start_date_oth = start_date), 
          comp_rate_alt %>%
              mutate(fyear = fyear - 1) %>%
              select(fyear,
                     gvkey_oth = gvkey, 
                     comp_oth = comp, 
                     file_name_oth = file_name, 
                     last_update_oth = last_update, 
                     start_date_oth = start_date)) %>%
    union(.,
          comp_rate_alt %>%
              mutate(fyear = fyear + 1) %>%
              select(fyear,
                     gvkey_oth = gvkey, 
                     comp_oth = comp, 
                     file_name_oth = file_name, 
                     last_update_oth = last_update, 
                     start_date_oth = start_date)) %>%
    distinct() %>%
    compute()

comp_tnic3 <- 
    comp_rate_alt %>%
    select(-comp) %>%
    left_join(tnic3 %>%
                  mutate(gvkey = sql("LPAD(gvkey1::text, 6, '0')"), 
                         gvkey_oth = sql("LPAD(gvkey2::text, 6, '0')")) %>%
                  rename(fyear = year) %>%
                  select(fyear, gvkey, gvkey_oth, score)) %>%
    # Merge on gvkey_oth and {fyear, fyear - 1}
    left_join(comp_oth) %>%
    # Keep calls with start_date_oth in (start_date - 3 month; start_date]
    filter(sql("start_date - interval '3 months'") < start_date_oth & 
               start_date_oth <= start_date) %>%
    distinct() %>%
    # Compute score-weighted competition rate
    # Self scores are NA, i.e., not part of the computation
    group_by(file_name, last_update) %>%
    summarize(comp_tnic3_i = sum(score*comp_oth)) %>%
    ungroup() %>%
    compute(index = list(c("file_name", "last_update")))

# SIC-based industry competition
# Use SICH	NUM	SICH -- Standard Industrial Classification - Historical

sic <- 
    fundann %>%
    # Extract 3-digit and 4-digit SIC
    mutate(sic4 = sql("LPAD(sich::text, 4, '0')")) %>%
    mutate(sic3 = sql("substring(sic4 from 1 for 3)")) %>%
    select(gvkey, fyear, sic3, sic4, ggroup) %>%
    filter(!is.na(sic4)) %>%
    distinct() %>%
    compute()

comp_sic <- 
    comp_rate_alt %>%
    left_join(sic %>%
                  select(gvkey, fyear, sic4, sic3, ggroup)) %>%
    compute()

# WARNING: allow merge on {fyear, fyear - 1, fyear + 1}
comp_sic_long <- 
    union(comp_sic, 
          comp_sic %>%
              mutate(fyear = fyear - 1)) %>%
    union(., 
          comp_sic %>%
              mutate(fyear = fyear + 1)) %>%
    distinct() %>%
    compute()

# Industry competition by sic3 (average)
comp_sic3 <- 
    comp_sic %>%
    select(-sic4, -ggroup, -comp) %>%
    left_join(comp_sic_long %>%
                  select(sic3, fyear, comp,
                         start_date_oth = start_date,
                         gvkey_oth = gvkey)) %>%
    # Keep calls with start_date_oth in (start_date - 3 month; start_date]
    filter(sql("start_date - interval '3 months'") < start_date_oth & 
               start_date_oth <= start_date) %>%
    # Exclude company's own calls
    filter(gvkey != gvkey_oth) %>%
    distinct() %>%
    # Average by {file_name, last_update, start_date} already merged on {sic3}
    group_by(file_name, last_update) %>%
    summarize(comp_sic3_i = mean(comp)) %>%
    ungroup() %>%
    compute(index = list(c("file_name", "last_update")))

# Industry competition by sic4 (average)
comp_sic4 <- 
    comp_sic %>%
    select(-sic3, -ggroup, -comp) %>%
    left_join(comp_sic_long %>%
                  select(sic4, fyear, comp,
                         start_date_oth = start_date, 
                         gvkey_oth = gvkey)) %>%
    # Keep calls with start_date_oth in (start_date - 3 month; start_date]
    filter(sql("start_date - interval '3 months'") < start_date_oth & 
               start_date_oth <= start_date) %>%
    # Exclude company's own calls
    filter(gvkey != gvkey_oth) %>%
    distinct() %>%
    # Average by {file_name, last_update, start_date} already merged on {sic4}
    group_by(file_name, last_update) %>%
    summarize(comp_sic4_i = mean(comp)) %>%
    ungroup() %>%
    compute(index = list(c("file_name", "last_update")))

# Industry competition by GICS ggroup (average)

comp_ggroup <- 
    comp_sic %>%
    select(-sic3, -sic4, -comp) %>%
    left_join(comp_sic_long %>%
                  select(ggroup, fyear, comp,
                         start_date_oth = start_date, 
                         gvkey_oth = gvkey)) %>%
    # Keep calls with start_date_oth in (start_date - 3 month; start_date]
    filter(sql("start_date - interval '3 months'") < start_date_oth & 
               start_date_oth <= start_date) %>%
    # Exclude company's own calls
    filter(gvkey != gvkey_oth) %>%
    distinct() %>%
    # Average by {file_name, last_update, start_date} already merged on {ggroup}
    group_by(file_name, last_update) %>%
    summarize(comp_ggroup_i = mean(comp)) %>%
    ungroup() %>%
    compute(index = list(c("file_name", "last_update")))

# Get earnings surprises data ---- 

ibes_mb_alt <- 
    ibes_mb %>%
    select(permno, fq_end = pends, 
           ce_avg, is_meet_avg, is_just_meet_avg, 
           ce_med, is_meet_med, is_just_meet_med, disp, consens_n) %>%
    distinct() %>%
    compute(index = list(c("permno", "fq_end")))

# Get fundann annual data ----

fundann_alt <- 
    fundann %>%
    select(gvkey, fyear, year,
           fy_end = datadate, 
           fic, exchg, # Incorporation and exchange
           gsector, ggroup, sich, sic3, sic4, # Industry
           sale, at, mcap, # Size
           ib_to_at, loss, ret_fr, # Profitability
           market_to_book, sale_gr1, sale_gr3, # Growth
           xrd_to_sale, fai_fr # Investment
    ) %>%
    # Compute "type"
    mutate(type_ib = sql("AVG(ib_to_at) OVER(PARTITION BY gvkey ORDER BY fyear ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)")) %>%
    compute(index = c("gvkey", "fyear"))

# Get fundqtr quarterly data ----

fundqtr_alt <- 
    fundqtr %>%
    select(gvkey, fyear = fyearq, fqtr, 
           fq_end = datadate, # Fiscal quarter end
           mbq, ibq_to_atq, lossq, mcapq, atq, saleq, ibq,
           sic3q = sic3, sic4q = sic4, # Industry
           hhi3, hhi4, # HHI indices
           lev1q, levq, dd1q_to_cheq, 
           sstkq_sc, dltisq_sc) %>%
    # Compute "type" over the next 4 quarters
    mutate(sstkq_sc4 = sql("SUM(sstkq_sc) OVER (PARTITION BY gvkey ORDER BY fyear, fqtr ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING)"), 
           dltisq_sc4 = sql("SUM(dltisq_sc) OVER (PARTITION BY gvkey ORDER BY fyear, fqtr ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING)"),
           ibq_to_atq_lead = sql("LEAD(ibq_to_atq) OVER (PARTITION BY gvkey ORDER BY fq_end)")) %>%
    compute(index = c("gvkey", "fyear", "fqtr"))

# Get CRSP quarterly data ----

crsp_calls_alt <- 
    crsp_calls %>%
    select(-matches("^n_"))

# Get {gvkey, fyear} for Hoberg-Phillips data ----

hhi_fluid <-
    hhi %>%
    full_join(fluid) %>%
    mutate(gvkey = sql("lpad(gvkey::text, 6, '0')")) %>%
    # year in hp is calendar year extracted from datadate
    inner_join(fundann_alt %>%
                   select(gvkey, year, fyear)) %>%
    select(-year) %>%
    select(gvkey, fyear, tnic3tsimm) %>%
    compute(index = c("gvkey", "fyear"))

# Executive title, tenure salary, bonus
executives_alt <- 
    # Keep unique CEOs per firm-year ---
    executives %>%
    filter(is_ceo) %>%
    select(company_id, management_id, executive_id, fyear, tenure) %>%
    distinct() %>%
    # For years with multiple CEOs; keep the one with the longest tenure
    group_by(company_id, fyear) %>%
    filter(tenure == max(tenure)) %>%
    ungroup() %>%
    #  or smaller executive_id
    group_by(company_id, fyear) %>%
    filter(executive_id == min(executive_id)) %>%
    ungroup() %>%
    # Get CEO compensation ---
    left_join(executives) %>%
    select(company_id, management_id, executive_id, fyear, is_ceo,
           tenure, salary, bonus, tdc1_cash, stock_awards) %>%
    compute(index = list(c("company_id", "fyear"), 
                         c("company_id", "management_id", "fyear")))

equity_calc_alt <- 
    equity_calc %>%
    select(company_id, management_id, fyear, 
           value_shares_held, 
           value, value_log_gr1, value_vested, value_opt_new, # New opt grants
           delta, delta_log_gr1, delta_vested, delta_opt_new, 
           vega, vega_log_gr1, vega_vested, vega_opt_new) %>%
    distinct()

# Capital issuance after the call ----

ciq_offerings_alt <- 
    ciq_offerings %>%
    mutate(ann_year = sql("extract(year from announcedate)")) %>%
    compute()

ciq_offerings_long <- 
    ciq_offerings_alt %>%
    select(gvkey, announcedate, is_ciq_debt, is_ciq_equity, is_ciq_private, is_ciq_offering, ann_year) %>%
    union(., 
          ciq_offerings_alt %>%
              mutate(ann_year = ann_year - 1) %>%
              select(gvkey, announcedate, is_ciq_debt, is_ciq_equity, is_ciq_private, is_ciq_offering, ann_year)) %>%
    distinct() %>%
    arrange(gvkey, announcedate) %>%
    compute()

# Capital issuance within {1, 3, 12} months after the call
ciq_offerings_lead <- 
    se_link %>%
    select(file_name, last_update, start_date, gvkey, fq_end) %>%
    mutate(ann_year = sql("extract(year from start_date)"), 
           start_date1 = sql("start_date + interval '1 month'"), 
           start_date3 = sql("start_date + interval '3 month'"),  
           start_date12 = sql("start_date + interval '12 month'")) %>%
    left_join(ciq_offerings_long) %>%
    # within next 1 month
    mutate(is_ciq_debt1 = if_else(start_date <= announcedate & announcedate <= start_date1, is_ciq_debt, FALSE), 
           is_ciq_equity1 = if_else(start_date <= announcedate & announcedate <= start_date1, is_ciq_equity, FALSE),
           is_ciq_offering1 = if_else(start_date <= announcedate & announcedate <= start_date1, is_ciq_offering, FALSE),
           is_ciq_private1 = if_else(start_date <= announcedate & announcedate <= start_date1, is_ciq_private, FALSE)) %>%
    # within next 3 months
    mutate(is_ciq_debt3 = if_else(start_date <= announcedate & announcedate <= start_date3, is_ciq_debt, FALSE), 
           is_ciq_equity3 = if_else(start_date <= announcedate & announcedate <= start_date3, is_ciq_equity, FALSE),
           is_ciq_offering3 = if_else(start_date <= announcedate & announcedate <= start_date3, is_ciq_offering, FALSE),
           is_ciq_private3 = if_else(start_date <= announcedate & announcedate <= start_date3, is_ciq_private, FALSE)) %>%
    # within next 12 months
    mutate(is_ciq_debt12 = if_else(start_date <= announcedate & announcedate <= start_date12, is_ciq_debt, FALSE), 
           is_ciq_equity12 = if_else(start_date <= announcedate & announcedate <= start_date12, is_ciq_equity, FALSE),
           is_ciq_offering12 = if_else(start_date <= announcedate & announcedate <= start_date12, is_ciq_offering, FALSE),
           is_ciq_private12 = if_else(start_date <= announcedate & announcedate <= start_date12, is_ciq_private, FALSE)) %>%
    select(-is_ciq_debt, -is_ciq_equity, -is_ciq_offering, -is_ciq_private) %>%
    # Collapse by {gvkey, fq_end}
    group_by(file_name, last_update, start_date, gvkey, fq_end) %>%
    summarize_at(.vars = vars(matches("is_ciq_debt|is_ciq_equity|is_ciq_offering|is_ciq_private")), 
                 .funs = funs(bool_or(.))) %>%
    ungroup() %>%
    mutate_at(.vars = vars(matches("is_ciq_debt|is_ciq_equity|is_ciq_offering|is_ciq_private")),
              .funs = funs(coalesce(., FALSE))) %>%
    # Get forward issuance magnitudes between Q+1 and Q+4
    left_join(fundqtr_alt %>%
                  select(gvkey, fq_end, sstkq_sc4, dltisq_sc4)) %>%
    # Aggregate proxy for the magnitude of issuance scaled by total assets
    mutate(ciq_debt12 = if_else(is_ciq_debt12, dltisq_sc4, 0), 
           ciq_equity12 = if_else(is_ciq_equity12, sstkq_sc4, 0), 
           ciq_offering12 = ciq_debt12 + ciq_equity12,
           # Private placement can be debt or equity
           ciq_private12 = if_else(is_ciq_private12, (dltisq_sc4 + sstkq_sc4), 0)) %>%
    compute()

# Compute nonanswer data -----

# WARNING: fyear is fiscal year of a call (contemporanous fyear call charactericstics)

# Merge non-answer data to {company_id, fy_end}
nonans <- 
    nonans_rate %>%
    inner_join(se_link) %>%
    # Require min participation
    filter(all_ans >= all_ans_min) %>%
    select(file_name, last_update, fyear, fqtr, fq_end, call_mon, call_yr, call_yr_mon,
           all_ans, nonans, refuse, unable, aftercall, matches("_rate"), 
           words_ans_avg) %>%
    distinct() %>%
    compute(index = list(c("file_name", "last_update")))

# Merge firms, executives, and calls ----

dbGetQuery(pg, "DROP TABLE IF EXISTS calls_merged")

merged <- 
    crsp_calls_alt %>%
    inner_join(nonans) %>%
    compute(index = list(c("gvkey", "fyear", "fqtr"))) %>%
    # Contemporaneous fundqtr
    left_join(fundqtr_alt) %>%
    compute(index = list(c("gvkey", "fq_end"))) %>%
    # Merge debt/equity issuance
    left_join(ciq_offerings_lead) %>%
    # Quarterly earnings surprise data
    left_join(ibes_mb_alt) %>%
    compute(index = list(c("gvkey", "fyear"))) %>%
    # WARNING: Lag annual data, i.e., for Q1 2002, use annual data from fiscal year 2001
    # Lagged fundann, hhi_fluid, CEO equity data
    left_join(fundann_alt %>%
                  select(-fy_end, -year) %>%
                  mutate(fyear = fyear + 1) %>%
                  compute(index = list(c("gvkey", "fyear")))) %>%
    compute(index = list(c("gvkey", "fyear"))) %>%
    left_join(hhi_fluid %>%
                  mutate(fyear = fyear + 1) %>%
                  compute(index = list(c("gvkey", "fyear")))) %>%
    compute(index = list(c("company_id", "fyear"))) %>%
    left_join(executives_alt %>%
                  mutate(fyear = fyear + 1) %>%
                  compute(index = list(c("company_id", "fyear")))) %>%
    compute(index = list(c("company_id", "fyear", "management_id"))) %>%
    left_join(equity_calc_alt %>%
                  mutate(fyear = fyear + 1) %>%
                  compute(index = list(c("company_id", "fyear", "management_id")))) %>%
    compute(index = list(c("file_name", "last_update"))) %>%
    # Contemporaneous call-level competition rate
    left_join(comp_rate_alt %>%
                  select(file_name, last_update, comp)) %>%
    compute(index = list(c("file_name", "last_update"))) %>%
    left_join(comp_tnic3) %>%
    compute(index = list(c("file_name", "last_update"))) %>%
    left_join(comp_sic3) %>%
    compute(index = list(c("file_name", "last_update"))) %>%
    left_join(comp_sic4) %>%
    compute(index = list(c("file_name", "last_update"))) %>%
    left_join(comp_ggroup) %>%
    compute(index = list(c("ggroup"))) %>%
    # Growth of import from China by SIC3
    left_join(import_sic3 %>%
                  filter(is_usa) %>%
                  select(fyear = year, sic3, matches("impgr1|impgr3")) %>%
                  rename(impgr1_us3 = impgr1) %>%
                  rename(impgr3_us3 = impgr3) %>%
                  rename(impgr1_us3_lag = impgr1_lag) %>%
                  rename(impgr3_us3_lag = impgr3_lag)) %>%
    left_join(import_sic3 %>%
                  filter(!is_usa) %>%
                  select(fyear = year, sic3, matches("impgr1|impgr3")) %>%
                  rename(impgr1_oth3 = impgr1) %>%
                  rename(impgr3_oth3 = impgr3) %>%
                  rename(impgr1_oth3_lag = impgr1_lag) %>%
                  rename(impgr3_oth3_lag = impgr3_lag)) %>%
    compute(index = list(c("year", "sic4"))) %>%
    # Growth of import from China by SIC4
    left_join(import_sic4 %>%
                  filter(is_usa) %>%
                  select(fyear = year, sic4, matches("impgr1|impgr3")) %>%
                  rename(impgr1_us4 = impgr1) %>%
                  rename(impgr3_us4 = impgr3) %>%
                  rename(impgr1_us4_lag = impgr1_lag) %>%
                  rename(impgr3_us4_lag = impgr3_lag)) %>%
    left_join(import_sic4 %>%
                  filter(!is_usa) %>%
                  select(fyear = year, sic4, matches("impgr1|impgr3")) %>%
                  rename(impgr1_oth4 = impgr1) %>%
                  rename(impgr3_oth4 = impgr3) %>%
                  rename(impgr1_oth4_lag = impgr1_lag) %>%
                  rename(impgr3_oth4_lag = impgr3_lag)) %>%
    left_join(sp1500) %>%
    left_join(rscript) %>%
    compute(index = list(c("company_id", "fyear", "fqtr"), 
                         c("file_name", "start_date")))

# Impose universal filters and write to PG
# WARNING: {fic, exchg, sich, gsector} filters as of the beginning
# of the fiscal year, i.e., for Q1 2002, as of the end of the fiscal year 2001
calls_merged <- 
    merged %>% 
    # Require all firm identifiers non-missing
    filter(!is.na(company_id) & !is.na(management_id) & !is.na(fyear)) %>%
    # Keep CEOs
    filter(is_ceo) %>%
    # Country of incorporation fic = 'USA'
    filter(fic == 'USA') %>%
    # Firms listed on NYSE, AMEX, NASDAQ as in AuditAnalytics
    # 11	New York Stock Exchange
    # 12	American Stock Exchange
    # 14	NASDAQ-NMS Stock Market
    filter(sql("(exchg && ARRAY[11, 12, 14]::text[])")) %>%
    # Drop penny stock prc_unadj <= 2
    # filter(prc_unadj >= 2) %>%
    # Drop finance and utilities
    # financial firms (SIC codes 6000 to 6799)
    filter(!(6000 <= sich & sich <= 6799)) %>%
    # utilities (SIC codes 4900 to 4949)
    filter(!(4900 <= sich & sich <= 4949)) %>%
    # Exclude also GICS for {Financials, Utility}
    filter(gsector != '40' & gsector != '55') %>%
    # WARNING: Restrict to Equilar time-period
    filter(fyear >= 2000 & fyear <= 2016) %>%
    # Set missing patent xi to 0 (xi ix collected for CRSP universe)
    distinct() %>%
    compute(name = "calls_merged", temporary = FALSE,
            index = list(c("company_id", "fyear"), 
                         c("company_id", "management_id", "fyear")))

db_comment <- paste0("CREATED USING azakolyukina/nonans_product/create_calls_merged.R ON ", Sys.time())
dbGetQuery(pg, paste0("COMMENT ON TABLE calls_merged IS '",
                      db_comment, "';"))

dbGetQuery(pg, "ALTER TABLE calls_merged OWNER TO nonans_product")
dbGetQuery(pg, "GRANT SELECT ON calls_merged TO nonans_product_access")
dbGetQuery(pg, "VACUUM ANALYZE calls_merged")

dbDisconnect(pg)
