Changes

You will need to run the queries below to build out a master table that has dead and alive flags and the company counts for each year in the database by datefirstinv. CREATE TABLE stageflagscore AS SELECT *, CASE WHEN seedflag = 1 OR earlyflag = 1 OR laterflag = 1 THEN 1::int ELSE 0::int END AS selflag FROM stageflags; --143347

DROP TABLE selcos; CREATE TABLE selcos AS SELECT DISTINCT coname, statecode, datefirstinv, selflag FROM stageflagscore WHERE excludeflag = 0 AND selflag = 1; --32597

DROP TABLE deadalive; CREATE TABLE deadalive AS SELECT deaddate1.*, sel.selflag FROM deaddate1 LEFT JOIN selcos AS sel ON deaddate1.coname = sel.coname AND deaddate1.statecode = sel.statecode AND deaddate1.datefirstinv = sel.datefirstinv; --44740

--match to sel flag DROP TABLE deadalivesel; CREATE TABLE deadalivesel AS SELECT da.*, flags.stage3, flags.seedflag, flags.earlyflag, flags.laterflag, flags.growthflag, flags.transactionflag, flags.excludeflag FROM deadalive AS da LEFT JOIN stageflags AS flags ON da.coname = flags.coname AND da.statecode = flags.statecode AND da.datefirstinv = flags.datefirstinv; --143310

CREATE TABLE deadalive1 as SELECT coname, city, statecode, datefirstinv, datelastinv, deaddate, extract(year from datefirstinv) as aliveyear, extract(year from deaddate) AS deadyear FROM deadalive WHERE selflag=1; --32575

DROP TABLE tempbase; CREATE TABLE tempbase As SELECT DISTINCT year, coname, city, statecode FROM allyears JOIN deadalive1 ON year>=extract(year from datefirstinv) AND year<=deadyear; --239446

DROP TABLE alivecount; CREATE TABLE alivecount AS SELECT city, statecode, year, count(coname) as numalive FROM tempbase GROUP BY city, statecode, year ORDER by count(coname) DESC; --42296 \COPY alivecount TO 'alivecount.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV