220
edits
Changes
Jump to navigation
Jump to search
→Build dead/alive flags
--42296
\COPY alivecount TO 'alivecount.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==coleveloutput==
One of the output tables required by the other researchers is the coleveloutput table. It contains company, geo and ipo/ma details in the form of aliveyear, deadyear. Here's how you build it:
DROP TABLE SelFlagBase;
CREATE TABLE SelFlagBase AS
SELECT DISTINCT coname, statecode, datefirstinv from stageflags where growthflag=1;
--32597
DROP TABLE companybasecore2;
CREATE TABLE companybasecore2 AS
SELECT companybasecore.*,
CASE WHEN SELFlagbase.coname IS NOT NULL THEN 1::int ELSE 0::int END AS hadgrowthvc
FROM companybasecore
LEFT JOIN SelFlagBase ON SelFlagBase.coname=companybasecore.coname AND SelFlagBase.statecode=companybasecore.statecode AND
SelFlagBase.datefirstinv=companybasecore.datefirstinv;
--44740
SELECT COUNT(*) FROM companybasecore2 WHERE hadgrowthvc=1;
--32575
DROP TABLE coleveloutput;
CREATE TABLE coleveloutput AS
SELECT companybasecore2.coname, companybasecore2.statecode, companybasecore2.datefirstinv, companybasecore2.city,
companybasecore2.addr1, companybasecore2.addr2, companybasecore2.zip, g.latitude, g.longitude, d.deaddate, d.aliveyear, d.deadyear
FROM companybasecore2
LEFT JOIN deadalive1 AS d ON d.coname=companybasecore2.coname AND d.statecode=companybasecore2.statecode AND
d.datefirstinv=companybasecore2.datefirstinv
LEFT JOIN geoimport AS g ON g.coname = companybasecore2.coname AND g.statecode = companybasecore2.statecode AND g.datefirstinv =
companybasecore2.datefirstinv
WHERE hadgrowthvc=1;
--32575
\COPY coleveloutput TO 'coleveloutput.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Creating Stage Flags Table==
