220
edits
Changes
Jump to navigation
Jump to search
→Creating Base Tables
CREATE TABLE companybase AS
SELECT DISTINCT
coname,updateddate,foundingdate,datelastinv,datefirstinv,investedk,city,description,msa,msacode,nationcode,statecode,addr1,ad dr2addr2,indclass,indsubgroup3,indminor,url,zip
FROM roundbase
ORDER BY coname;
FROM roundbase
ORDER BY coname;
==Creating Stage Flags Table==
Stage flags will be used to later on to determine if a company received seed, early or later stage financing. The growthflag is '1' if either the seed, early or later flags is '1'. The exclude flag is used to exclude all companies that received financing for activities we are not interested in and thus should be excluded from our dataset. Entries like 'Open Market Purchase', 'PIPE', etc are the things that the exclude flag filters out. It is built off the round table.
DROP TABLE stageflags;
CREATE TABLE stageflags AS
SELECT coname, statecode, datefirstinv, rounddate, stage3,
CASE
WHEN stage3 = 'Seed' THEN 1::int
ELSE 0::int
END AS seedflag,
CASE
WHEN stage3 = 'Early Stage' THEN 1::int
ELSE 0::int
END AS earlyflag,
CASE
WHEN stage3 = 'Later Stage' THEN 1::int
ELSE 0::int
END AS laterflag,
CASE
WHEN stage3 = 'Seed' OR stage3 = 'Later Stage' OR stage3 = 'Early Stage' THEN 1::int
ELSE 0::int
END AS growthflag,
CASE
WHEN stage3 = 'Acq. for Expansion' OR stage3 = 'Acquisition' OR stage3 = 'Bridge Loan' OR stage3 = 'Expansion' OR stage3 = 'Pending Acq' OR stage3 = 'Recap or Turnaround' OR stage3 = 'Mezzanine' THEN 1::int
ELSE 0::int
END AS transactionflag,
CASE
WHEN stage3 = 'LBO' OR stage3 = 'MBO' OR stage3 = 'Open Market Purchase' OR stage3 = 'PIPE' OR stage3 = 'Secondary Buyout'
OR stage3 = 'Other' OR stage3 = 'VC Partnership' OR stage3 = 'Secondary Purchase' THEN 1::int
ELSE 0::int
END AS excludeflag
FROM round;
