220
edits
Changes
Jump to navigation
Jump to search
→Build dead/alive flags
FROM deaddate;
--44740
You will need to run the queries below to
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
==Creating Stage Flags Table==
