220
edits
Changes
Jump to navigation
Jump to search
→coleveloutput
--42296
\COPY alivecount TO 'alivecount.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Creating 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;
--32575
\COPY coleveloutput TO 'coleveloutput.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Creating roundplus==
DROP TABLE roundplus;
CREATE TABLE roundplus AS
SELECT roundcore.*, seedflag, earlyflag, laterflag, growthflag, transactionflag, excludeflag,
CASE WHEN roundcore.datefirstinv=roundcore.rounddate THEN 1::int ELSE 0::int END as dealflag,
CASE WHEN SELFlagbase.coname IS NOT NULL THEN 1::int ELSE 0::int END AS hadgrowthvc,
extract(year from roundcore.rounddate) as roundyear,
CASE WHEN rndamtdisck IS NOT NULL THEN rndamtdisck/1000 WHEN rndamtdisck IS NULL AND rndamtestk IS NOT NULL THEN rndamtestk/1000 ELSE
NULL::real END as roundamtm
FROM roundcore
LEFT JOIN SelFlagBase ON SelFlagBase.coname=roundcore.coname AND SelFlagBase.statecode=roundcore.statecode AND
SelFlagBase.datefirstinv=roundcore.datefirstinv
LEFT JOIN stageflags ON stageflags.coname=roundcore.coname AND stageflags.statecode=roundcore.statecode AND
stageflags.datefirstinv=roundcore.datefirstinv AND stageflags.rounddate=roundcore.rounddate;
--143001
SELECT coname, rounddate FROM (SELECT coname, rounddate FROM roundplus)a
GROUP BY coname, rounddate
HAVING COUNT(*) > 1;
DELETE FROM roundplus WHERE coname = 'New York Digital Health LLC';
--2
==Cleaning round table==
