7,612
edits
Changes
Jump to navigation
Jump to search
Database is named vcdb2. It is located in /bulk/VentureCapitalData/SDCVCData. Launch with psql vcdb2. Load the following tables by running the commands below. Make sure the sql scripts and data txt files are all located in the folder. Check that the line numbers copied into your new tables match the line numbers in the Load files.
So first check ==Redoing the companybase with the new SDC company data==Since we did another pull in SDC to see if get the key is valid on correct city and addresses. We need to update the base data using companybasecore table which means we need to clean the new companybase. Then this will recreate the following 2 queriesroundplus and roundlevel outputs.
→Creating portcoexitmaster
==File locations==
Database files are located here:
Z:\VentureCapitalData\SDCVCData\vcdb2
SDC files are located here and the normalized versions are copied into the Z folder above:
E:\McNair\Projects\VC Database
Database can be started by typing psql vcdb2
The file containing all the SQL queries used to build vcdb2 is located in the Z drive and named ProcessData2.sql.
Z:\VentureCapitalData\SDCVCData\vcdb2\ProcessData2.sql
==Plan==
Rebuild roundbase, round, geo, ipos, mas from SDC data.
==Loading starting data into database==
\i LoadFunds.sql
\i LoadIPOs.sql
\i LoadMAs.sql
\i LoadRoundbase.sql
\i LoadFirms.sql
\i LoadLongDescription.sql
\i LoadRound.sql
==Cleaning Process==
The roundbase table which is used to build the core company and round tables contains some data that we would like to remove like Undisclosed companies and duplicate entries. In order to find what to clean, build your companybase table first. You know your companybase table is clean once it contains a 1:1 relationship between keys and entries. We will then apply these changes to the roundbase table because any cleaning changes made downstream should be incorporated upstream into the base table. Otherwise when you build anything else off your roundbase table, dirty keys will infect the other areas of your database. Once the roundbase table is clean we will rename it roundbasecore so that we know it is clean and good to use for building other core tables.
==Creating Base Tables==
FROM roundbase
ORDER BY coname;
==Cleaning the Companybase table==
Every table will contain some duplicate keys and erroneous entries. We're going to clean the companybase table so that every key (coname, statecode, datefirstinv) is unique. This means that there will be a 1:1 relationship between 1 key and 1 entry. Given an entry you will be able to create a unique key and given a coname, statecode, datefirstinv key you will be able to find exactly 1 entry that the key corresponds to in the companybase table set.
So first check to see if the key is valid on the base data using the following 2 queries.
SELECT COUNT(*)
FROM (SELECT coname, statecode, datefirstinv FROM companybase)a;
--44774
SELECT COUNT(*)
FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybase)a;
--44771
You can see that they key is not unique because the counts don't match up. There are 44,771 distinct keys but there are 44,774 keys in the companybase table. So 1 key can match to more than one entry in the table.
Some of the data in the companybase table contains undisclosed company names and companies that exist in other countries outside the US. So let's build flags for these two events and check the key count again.
DROP TABLE companybase1;
CREATE TABLE companybase1 AS
SELECT *,
CASE
WHEN nationcode = 'US' THEN 1::int
ELSE 0::int
END AS alwaysusflag,
CASE
WHEN coname = 'Undisclosed Company' THEN 1::int
ELSE 0::int
END AS undisclosedflag
FROM companybase;
SELECT COUNT(*)
FROM (SELECT coname, statecode, datefirstinv FROM companybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0)a;
--44771
SELECT COUNT(*)
FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0)a;
--44770
By looking at the counts you can see that there is still 1 duplicate key in the table. Let's find it another way. Running the query below finds the key (coname, statecode, datefirstinv) that appears twice in the table.
SELECT *
FROM (SELECT coname, statecode, datefirstinv FROM companybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0)AS key
GROUP BY coname, statecode, datefirstinv
HAVING COUNT(key) > 1;
The output looks like this:
coname | statecode | datefirstinv
----------------------------+-----------+--------------
New York Digital Health LLC | NY | 2015-08-13
We'll have to copy companybase1 out of the db and have a look on textpad for something unique about one of the entries on New York Digital Health LLC that we can use to manually delete it from the companybase1 table. Turns out the url is different so we'll use that.
Manually delete this record from the roundbase table using the below command. Now we're ready to build the companybasecore table.
DELETE FROM roundbase WHERE coname = 'New York Digital Health LLC' AND statecode = 'NY' AND datefirstinv = to_date('2015-08-13', 'YYYY-MM-DD') AND url = 'www.digitalhealthaccelerator.c';
==companybasecore table==
The queries below build your companybasecore table. The where clause takes the place of the 2 flags on nationcode and undisclosed company we built in companybase1 table. This table has a guaranteed 1:1 relationship between coname, statecode, datefirstinv and an entry in the table. The two queries at the end verify this. We use core tables to run joins later on.
DROP TABLE companybasecore;
CREATE TABLE companybasecore AS
SELECT DISTINCT
coname,updateddate,foundingdate,datelastinv,datefirstinv,investedk,city,description,msa,msacode,nationcode,statecode,addr1,addr2,indclass,indsubgroup3,indminor,url,zip
FROM roundbase WHERE nationcode = 'US' AND coname != 'Undisclosed Company';
--44740
--recheck keys
SELECT COUNT(*)
FROM (SELECT coname, statecode, datefirstinv FROM companybasecore)a;
--44740
SELECT COUNT(*)
FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybasecore)a;
--44740
==Cleaning ipos table==
Check to see if the existing keys in the table are valid. We are using issuer, issuedate, statecode as the key.
SELECT COUNT(*)
FROM (SELECT issuer, issuedate, statecode FROM ipos)a;
--10440
SELECT COUNT(*)
FROM (SELECT DISTINCT issuer, issuedate, statecode FROM ipos)a;
--9491
The keys are not unique so we must remove duplicate keys first. You will need to try different methods to isolate the duplicate keys. This is where you can be creative. I first started by finding the duplicates based on issuer, issuedate and statecode which is our key. Have a look in textpad/excel for ways to filter these keys. We would like to save as much information as possible so rather than excluding all these entries which sum to 1888 rows in the ipos table maybe there's some other way we can filter out records and still have distinct keys.
DROP TABLE ipoduplicates;
CREATE TABLE ipoduplicates AS
SELECT *, COUNT(*)
FROM (SELECT issuer, issuedate, statecode FROM ipos)a
GROUP BY issuer, issuedate, statecode
HAVING COUNT(*) > 1;
--939
\COPY ipoduplicates TO 'ipoduplicates.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV;
In the file you will notice that many keys contain different principalamts. Let's keep the MAX principal amount and throw out the same key that has a lower principalamt. This query is shown below.
DROP TABLE ipoinclude;
CREATE TABLE ipoinclude AS
SELECT issuer, issuedate, statecode, MAX(principalamt) AS principalamt
FROM ipos
GROUP BY issuer, issuedate, statecode;
--9470
Now use the ipoinclude table to create a ipocore table. Then check to see if this core table has unique keys so 1 key matches with 1 record. This is the defining characteristic of a core table.
DROP TABLE ipocore;
CREATE TABLE ipocore AS
SELECT ipos.issuer, ipos.issuedate, ipos.statecode
FROM ipos INNER JOIN ipoinclude ON ipos.issuer = ipoinclude.issuer AND ipos.issuedate = ipoinclude.issuedate AND
ipos.statecode = ipoinclude.statecode AND ipos.principalamt = ipoinclude.principalamt;
SELECT COUNT(*)
FROM (SELECT DISTINCT issuer, issuedate, statecode FROM ipocore)a;
You should notice that the ipocore table count does not match the count of DISTINCT keys. This means there are still some duplicates. So I created another duplicate table.
DROP TABLE ipoduplicates2;
CREATE TABLE ipoduplicates2 AS
SELECT *, COUNT(*)
FROM (SELECT issuer, issuedate, statecode FROM ipocore)a
GROUP BY issuer, issuedate, statecode
HAVING COUNT(*) > 1;
Then I created DELETE statements for all these entries. I deleted them from the ipoinclude table which will prevent these keys from appearing in the ipocore table when you JOIN the ipos with ipoinclude table.
--manually remove bad keys
DELETE FROM ipoinclude WHERE issuer = 'PacTel Corp' AND statecode = 'CA';
--1
DELETE FROM ipoinclude WHERE issuer = 'Templeton Dragon Fund Inc' AND statecode = 'FL';
--1
DELETE FROM ipoinclude WHERE issuer = 'Sterling Commerce' AND statecode = 'TX';
--1
DELETE FROM ipoinclude WHERE issuer = 'Sothebys Holdings Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'TD Waterhouse Group Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'Berlitz International Inc' AND statecode = 'NJ';
--1
DELETE FROM ipoinclude WHERE issuer = 'Spain Fund Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'Ultramar Corp' AND statecode = 'CT';
--1
DELETE FROM ipoinclude WHERE issuer = 'Goldman Sachs Group Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'Fidelity Advisor Korea Fund' AND statecode = 'MA';
--1
DELETE FROM ipoinclude WHERE issuer = 'Euronet Services Inc' AND statecode = 'KS';
--1
DELETE FROM ipoinclude WHERE issuer = 'Emerging Markets Tele Fund Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'FirstMiss Gold Inc' AND statecode = 'NV';
--1
DELETE FROM ipoinclude WHERE issuer = 'Templeton Vietnam Opportunitie' AND statecode = 'FL';
--1
DELETE FROM ipoinclude WHERE issuer = 'Hybridon Inc' AND statecode = 'MA';
--1
DELETE FROM ipoinclude WHERE issuer = 'Indonesia Fund Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'OpenTV Corp' AND statecode = 'CA';
--2
DELETE FROM ipoinclude WHERE issuer = 'Scudder New Europe Fund' AND statecode = 'NY';
--2
DELETE FROM ipoinclude WHERE issuer = 'Austria Fund Inc' AND statecode = 'NY';
--2
Now again JOIN your ipos table with your ipoinclude table and check the key count.
DROP TABLE ipocore;
CREATE TABLE ipocore AS
SELECT ipos.*
FROM ipos INNER JOIN ipoinclude ON ipos.issuer = ipoinclude.issuer AND ipos.issuedate = ipoinclude.issuedate AND
ipos.statecode = ipoinclude.statecode AND ipos.principalamt = ipoinclude.principalamt;
--9470
SELECT COUNT(*)
FROM (SELECT DISTINCT issuer, issuedate, statecode FROM ipocore)a;
--9470
The counts line up so now you should have a clean ipocore table!
==Cleaning mas table==
Check to see if you have bad keys in the table. The row count of the table should match up with count of distinct keys based on targetname, targetstatecode, announceddate.
SELECT COUNT(*)
FROM mas;
--114890
SELECT COUNT(*)
FROM (SELECT DISTINCT targetname, targetstatecode, announceddate FROM mas)a;
--114825
Great! The counts don't match so we'll have to clean the mas table. There is no obvious field to filter against with mas. So I inserted an id column in mas and took the MIN id for duplicate keys.
CREATE TABLE mas1 AS
SELECT *
FROM mas;
ALTER TABLE mas1 ADD COLUMN id SERIAL PRIMARY KEY;
ALTER TABLE mas ADD COLUMN id SERIAL PRIMARY KEY;
DROP TABLE masinclude;
CREATE TABLE masinclude AS
SELECT targetname, targetstatecode, announceddate, MIN(id) as id
FROM mas1
GROUP BY targetname, targetstatecode, announceddate;
--114825
DROP TABLE mascore;
CREATE TABLE mascore AS
SELECT mas.*
FROM mas INNER JOIN masinclude ON mas.id = masinclude.id;
--114825
SELECT COUNT(*)
FROM (SELECT DISTINCT targetname, targetstatecode, announceddate FROM mascore)a;
The mas distinct key count match the total count of the table so therefore the mascore table is clean.
==Name Based Matching companybase keys to mas keys==
Before attempting to match companybasecore with mascore you need a clean table or you will get many errors in the matcher output file. Luckily the core tables should already contain distinct keys if you've followed the process. However running the matcher will still yield many errors. So we will filter the mas keys some more. The first thing is to remove mas keys (targetname, announceddate, targetstatecode) where the announceddate falls within the same week. Keep the key that has the minimum announceddate and discard the higher date. Shown below:
DROP TABLE maskeys;
CREATE TABLE maskeys AS
SELECT DISTINCT targetname, targetstatecode, announceddate
FROM mascore;
--114825
DROP TABLE maskeysmindates;
CREATE TABLE maskeysmindates AS
SELECT targetname, targetstatecode, MIN(announceddate) AS announceddate
FROM mascore
GROUP BY targetname, targetstatecode;
--113236
DROP TABLE maskeysdatewindow;
CREATE TABLE maskeysdatewindow AS
SELECT maskeys.*, maskeysmindates.announceddate as minanndate,
CASE WHEN maskeys.announceddate - INTERVAL '7 day' > maskeysmindates.announceddate OR maskeys.announceddate =
maskeysmindates.announceddate THEN 1::int
ELSE 0::int
END AS dateflag
FROM maskeys LEFT JOIN maskeysmindates ON (maskeys.targetname = maskeysmindates.targetname AND
maskeys.targetstatecode = maskeysmindates.targetstatecode);
--114825
The dateflag is 1 when the current key's announceddate is 1 week older than the minimum announced date or it is the minimum announceddate for that targetname, targetstatecode pair. If the announceddate is less than 1 week greater than the minimum announceddate for te targetname, targetstatecode pair, then it is 0.
CREATE TABLE maskeysdatefiltered AS
SELECT targetname, targetstatecode, announceddate
FROM maskeysdatewindow
WHERE dateflag = 1;
--114794
\COPY maskeysdatefiltered TO 'maskeysdatefiltered.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
Grab the portco keys from the companybasecore table:
DROP TABLE portcokeys;
CREATE TABLE portcokeys AS
SELECT DISTINCT coname, statecode, datefirstinv
FROM companybasecore;
--44740
\COPY portcokeys TO 'portcokeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
Put the portcokeys and maskeysdatefiltered text files into the Matcher Input folder. For more instructions on how to run the Matcher see [[The Matcher (Tool)]]
==Fixing Errors in the Matcher Output for portco and mas==
You will still receive multiple warnings in the output.matched file. In Excel add flags to exclude if the announceddate < datefirstinv and another exclude flag if the datefirstinv = announceddate. Also add a warning flag if the Warning column is "Hall-Warning:Multiple". Then import this back into your db by creating a matcheroutput table.
DROP TABLE matcherportcomas;
CREATE TABLE matcherportcomas (
warning varchar(100),
file1coname varchar(100),
file1statecode varchar(2),
file1datefirstinv date,
file2targetname varchar(100),
file2targetstatecode varchar (2),
file2announceddate date,
excludeflag1 int,
excludeflag2 int,
warningflag int
);
\COPY matcherportcomas FROM 'matcheroutputportco-mas.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--9645
You've imported 9,645 matches into your matcher table in vcdb2 but if you run the query below you will get the number of "good" matches. These are matches that do not contain warnings, where the datefirstinv > announceddate for a merger/acquisition and where the datefirstinv does not equal the announceddate.
SELECT COUNT(*) FROM
(SELECT file1coname, file1statecode, file2targetname, file2targetstatecode FROM matcherportcomas WHERE excludeflag1 = 0 AND
excludeflag2 = 0 AND warningflag = 0)a;
--8291
As you can see we're throwing out a lot of the data in the matcher file (9645 -> 8291). So the next few queries will try and save as much of the bad matches as possible and add them back to the good matches to create our matcherportcomascore table.
Select the portco keys that are matched to the minimum announceddate for any mergers:
DROP TABLE matcherwarningmindates;
CREATE TABLE matcherwarningmindates AS
SELECT file1coname, file1statecode, file1datefirstinv, MIN(file2announceddate)
FROM matcherportcomas
WHERE excludeflag1 = 0 AND excludeflag2 = 0 AND warningflag = 1
GROUP BY file1coname, file1statecode, file1datefirstinv;
--364
Then using the temporary key (file1coname, file1statecode, file1datefirstinv, file2announceddate) join this back to the original matcher table to get the rest of the data we will want in the core table.
DROP TABLE matcherportcomasinclude;
CREATE TABLE matcherportcomasinclude AS
SELECT m.* FROM
matcherportcomas AS m INNER JOIN matcherwarningmindates AS mi ON m.file1coname = mi.file1coname AND m.file1statecode =
mi.file1statecode AND m.file1datefirstinv = mi.file1datefirstinv AND m.file2announceddate = mi.min
WHERE excludeflag1 = 0 AND excludeflag2 = 0 AND warningflag = 1;
--366
The inner join result should equal the amount in the matcherwarningmindates table but it doesn't. So to find the dirty entries we'll use the query below.
SELECT *, COUNT(*) FROM
(SELECT file1coname, file1statecode, file1datefirstinv FROM matcherportcomasinclude)a
GROUP BY file1coname, file1statecode, file1datefirstinv
HAVING COUNT(*) > 1;
file1coname | file1statecode | file1datefirstinv | count
-------------------------+----------------+-------------------+-------
PA Inc | TX | 2007-09-25 | 2
High Sierra Energy L.P. | CO | 2004-12-23 | 2
Find these records in the matcherportcomas table in Excel and delete 1 entry from each manually:
DELETE FROM matcherportcomasinclude WHERE file1coname = 'PA Inc' AND file1statecode = 'TX' AND file2targetname = 'PA Corp'
AND file2targetstatecode = 'VA';
--1
DELETE FROM matcherportcomasinclude WHERE file1coname = 'High Sierra Energy L.P.' AND file1statecode = 'CO' AND
file2targetname = 'High Sierra Energy GP LLC' AND file2targetstatecode = 'CO';
--1
Now we should have a clean matcherportcomasinclude table. To be sure check the number of distinct matches using the query below. It should be the same as the number of records in this table.
SELECT COUNT(*) FROM
(SELECT DISTINCT file1coname, file1statecode, file1datefirstinv FROM matcherportcomasinclude)a;
--364
SELECT COUNT(*) FROM matcherportcomasinclude;
--364
Looks good so let's UNION ALL to join the matcherportcomasinclude table with the matcherportcomas with all flags set to 0 to create the core table.
CREATE TABLE matcherportcomascore AS
SELECT *
FROM matcherportcomas WHERE excludeflag1 = 0 AND excludeflag2 = 0 AND warningflag = 0
UNION ALL
SELECT *
FROM matcherportcomasinclude;
--8655
Recheck the key counts. 1 portco key from the companybase table should match with exactly 1 mas key from the mascore table. If you have more than 1:1 you will get errors in the next phase when you join the companybase table to the mas table.
SELECT COUNT(*) FROM (
SELECT DISTINCT file1coname, file1statecode, file1datefirstinv
FROM matcherportcomascore) AS foo;
--8655
Great! Now you are ready to begin joining the companybase table to the mas table.
==Joining companybasekeys with maskeys and ipokeys==
Before doing this stage make sure the following is true:
#companybasecore, mascore, ipocore are clean core tables...They should be 1:1 on themselves. That means 1 key should match to one row in each respective table. See [[#Cleaning the Companybase table|Cleaning the Companybase table]] [[#Cleaning mas table|Cleaning mas table]] [[#Cleaning ipos table|Cleaning ipos table]] for instructions
#You've done name based matching on the keys in companybasecore and mascore and cleaned up the matcher output file. See [[#Name Based Matching companybase keys to mas keys|Name Based Matching companybase keys to mas keys]] and [[#Fixing Errors in the Matcher Output for portco and mas|Fixing Errors in the Matcher Output for portco and mas]]
#You've done name based matching on the keys in companybasecore and ipocore and cleaned up the matcher output file. See [[#Name Based Matching companybase keys to ipo keys|Name Based Matching companybase keys to ipo keys]] and [[#Fixing Errors in the Matcher Output for portco and ipo|Fixing Errors in the Matcher Output for portco and ipo]]
We want to join the three sets of keys together before grabbing other data from their respective tables because there will be collisions with the maskeys and ipokeys. Some companies will have ipos as well as mergers/acquisitions or the data might also be miss coded by SDC platinum. The problem for us is a company that has both an ipo and ma will cause our join row counts to increase every time we join with these duplicate keys. We want a portcokey to join with only one ipokey or maskey in our master table. Running the query below creates a table that contains the three sets of keys:
DROP TABLE companybasekeysaddmaskeysaddipokeys;
CREATE TABLE companybasekeysaddmaskeysaddipokeys AS
SELECT c.coname, c.statecode, c.datefirstinv, matcherm.file2targetname AS mastargetname, matcherm.file2targetstatecode AS masstatecode,
matcherm.file2announceddate AS announceddate, matcheri.file2issuer AS ipoissuer, matcheri.file2statecode AS ipostatecode,
matcheri.file2issuedate AS ipoissuedate FROM
companybasecore AS c LEFT JOIN matcherportcomascore as matcherm ON c.coname = matcherm.file1coname AND c.statecode =
matcherm.file1statecode AND c.datefirstinv = matcherm.file1datefirstinv
LEFT JOIN matcherportcoipocore AS matcheri ON c.coname = matcheri.file1coname AND c.statecode = matcheri.file1statecode AND
c.datefirstinv = matcheri.file1datefirstinv;
--44740
\COPY companybasekeysaddmaskeysaddipokeys TO 'companybasekeysaddmaskeysaddipokeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
Open in Excel and add a flag to see the rows with an ipokey as well as a maskey. You can use a formula like this: =IF(OR(ISBLANK(G518),ISBLANK(D518)),0,1). You'll see there are 83 portcokeys that match to an ipokey and a maskey. We'll write a query in sql to take the ipokey or maskey with the lowest date attached to it. This will be the exit date for that portco.
First we create a table that has the minimum exit date. Then we add flags to indicate when the ipokey is valid and when the maskey is valid. Then we create a companybasekeymaskeyipokeycore table that contains clean matches from companybasekey (portcokey) to ipo or mas.
DROP TABLE companybasekeysaddmaskeyaddipokeysmindate;
CREATE TABLE companybasekeysaddmaskeyaddipokeysmindate AS
SELECT *,
CASE
WHEN announceddate IS NOT NULL AND ipoissuedate IS NOT NULL THEN LEAST(announceddate,ipoissuedate)
WHEN announceddate IS NOT NULL THEN announceddate
WHEN ipoissuedate IS NOT NULL THEN ipoissuedate
END AS masterdate
FROM companybasekeysaddmaskeysaddipokeys;
--44740
\COPY companybasekeysaddmaskeyaddipokeysmindate TO 'companybasekeysaddmaskeyaddipokeysmindate.txt' WITH DELIMITER AS E'\t' HEADER NULL
AS '' CSV
DROP TABLE companybasekeysaddmaskeyaddipokeysmindateflag;
CREATE TABLE companybasekeysaddmaskeyaddipokeysmindateflag AS
SELECT keys.*,
CASE
WHEN announceddate = masterdate THEN 1::int
ELSE 0::int
END AS maskeyvalid,
CASE
WHEN ipoissuedate = masterdate THEN 1::int
ELSE 0::int
END AS ipokeyvalid
FROM companybasekeysaddmaskeyaddipokeysmindate as keys;
--44740
Now create the companybaseipokeycore and companybasemaskeycore tables using the flags created above.
DROP TABLE companybasekeymaskeycore;
CREATE TABLE companybasekeymaskeycore AS
SELECT c.coname, c.statecode, c.datefirstinv, c.mastargetname, c.masstatecode, c.announceddate
FROM companybasekeysaddmaskeyaddipokeysmindateflag AS c
WHERE maskeyvalid = 1;
--8610
DROP TABLE companybasekeyipokeycore;
CREATE TABLE companybasekeyipokeycore AS
SELECT c.coname, c.statecode, c.datefirstinv, c.ipoissuer, c.ipostatecode, c.ipoissuedate
FROM companybasekeysaddmaskeyaddipokeysmindateflag AS c
WHERE ipokeyvalid = 1;
--2312
To check if you have the correct number of ipo and mas keys add the two counts from your query above and the count from the query below and compare it to the number of keys in your companybasemaskeycore and companybaseipocore table. In my case I get 8610 + 2312 + 83 = 2350 + 8655.
SELECT COUNT(*)
FROM companybasekeysaddmaskeysaddipokeys
WHERE ipoissuedate IS NOT NULL AND announceddate IS NOT NULL;
--83
Now you can successfully join the companybasecore table to the ipocore and mascore tables through the companybasekeyipokeycore and companybasekeymaskeycore tables. With this step done you can create a master table which will contain information from companybase and ipo and mas.
==Creating companybaseipomasmaster table==
Before doing this stage make sure you have followed the steps in [[#Joining companybasekeys with maskeys and ipokeys|Joining companybasekeys with maskeys and ipokeys]]
You will be joining the companybasecore table with the mascore and ipocore through the companybasekeyipokey and companybasekeymaskey tables. The output master table will have each company name and the dates and amounts if they received a ipo or ma. As discussed in [[#Joining companybasekeys with maskeys and ipokeys|Joining companybasekeys with maskeys and ipokeys]] the master table includes the exit deal which had the minimum date so duplicate rows should not crop up in the master table.
DROP TABLE companybaseipomasmaster;
CREATE TABLE companybaseipomasmaster AS
SELECT c.coname, c.statecode, c.datefirstinv, c.investedk, c.city, c.addr1, c.addr2, ipokey.ipoissuedate, maskey.announceddate AS
masannounceddate, i.principalamt AS ipoprincipalamtk, m.transactionamt AS mastransactionamtk
FROM companybasecore AS c
LEFT JOIN companybasekeyipokeycore AS ipokey ON c.coname = ipokey.coname AND c.statecode = ipokey.statecode AND c.datefirstinv =
ipokey.datefirstinv
LEFT JOIN companybasekeymaskeycore AS maskey ON c.coname = maskey.coname AND c.statecode = maskey.statecode AND c.datefirstinv =
maskey.datefirstinv
LEFT JOIN ipocore AS i ON i.issuer = ipokey.ipoissuer AND i.issuedate = ipokey.ipoissuedate AND i.statecode = ipokey.ipostatecode
LEFT JOIN mascore AS m ON m.targetname = maskey.mastargetname AND m.targetstatecode = maskey.masstatecode AND m.announceddate =
maskey.announceddate;
--44740
\COPY companybaseipomasmaster TO 'companybaseipomasmaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
You can run checks on the ipo and mas counts to make sure everything joined properly. Any duplicate keys that were not cleaned up in previous steps will make this master table a complete mess due to all the joins so make sure you've followed the process fully. Below are some of the checks I ran:
SELECT COUNT(*) FROM companybaseipomasmaster WHERE masannounceddate IS NOT NULL;
--8610
SELECT COUNT(*) FROM companybaseipomasmaster WHERE mastransactionamtk IS NOT NULL;
--8610
SELECT COUNT(*) FROM companybaseipomasmaster WHERE ipoissuedate IS NOT NULL;
--2312
SELECT COUNT(*) FROM companybaseipomasmaster WHERE ipoprincipalamtk IS NOT NULL;
--2312
Everything looks good. These counts are compared against the key tables and core tables built in the previous steps.
==Name Based Matching companybase keys to ipo keys==
First verify that your keys in companybasecore and ipocore are unique by using the following queries. If not following instructions in these sections [[#Cleaning the Companybase table|Cleaning the Companybase table]] and [[#Cleaning ipos table|Cleaning ipos table]]
SELECT COUNT(*) FROM companybasecore;
--44740
SELECT COUNT(*) FROM
(SELECT DISTINCT coname, statecode, datefirstinv FROM companybasecore)a;
--44740
SELECT COUNT(*) FROM ipocore;
--9470
SELECT COUNT(*) FROM
(SELECT DISTINCT issuer, issuedate,statecode FROM ipocore)a;
--9470
Next export the keys to a text file and put in the Matcher input folder. Run the matcher on these files. For instructions on how to use the Matcher check this out [[The Matcher (Tool)]]
DROP TABLE portcokeys;
CREATE TABLE portcokeys AS
SELECT DISTINCT coname, statecode, datefirstinv
FROM companybasecore;
--44740
\COPY portcokeys TO 'portcokeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
DROP TABLE ipokeys;
CREATE TABLE ipokeys AS
SELECT issuer, statecode, issuedate
FROM ipocore;
--9470
\COPY ipokeys TO 'ipokeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Fixing Errors in the Matcher Output for portco and ipo==
After running the Matcher on your portcokeys and ipokeys you will notice there are some errors in the matched output file. Add flags in Excel that exclude rows where the issuedate < datefirstinv and where the issuedate = datefirstinv. If the exclude flag is 1 than you would want to exclude this entry from your table i.e. the issuedate > datefirstinv. If the flags are selected to 0, then you will want to keep this row. Also add a column for a warning flag that is 1 if the warning column is "Hall-Warning:Multiple". Next copy this txt file into the db by creating a new table.
DROP TABLE matcherportcoipo;
CREATE TABLE matcherportcoipo (
warning varchar(100),
file1coname varchar(100),
file1statecode varchar(2),
file1datefirstinv date,
file2issuer varchar(100),
file2statecode varchar (2),
file2issuedate date,
excludeflag1 int,
excludeflag2 int,
warningflag int
);
\COPY matcherportcoipo FROM 'matcherportco-ipos.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--2592
You can see the "good" matches by setting all the flags to 0 as shown in the query below.
SELECT COUNT(*)
FROM matcherportcoipo WHERE excludeflag1 = 0 AND excludeflag2 = 0 AND warningflag = 0;
--2313
We would like to add back all the data we can so let's have a look at the rows with multiple matches.
SELECT COUNT(*)
FROM matcherportcoipo WHERE excludeflag1 = 0 AND excludeflag2 = 0 AND warningflag = 1;
--66
Many of the duplicates have different issuedates so we'll just select the minimum issuedate for entries where the portcokey is matched twice.
DROP TABLE matcherportcoipomindate;
CREATE TABLE matcherportcoipomindate AS
SELECT file1coname, file1statecode, file1datefirstinv, MIN(file2issuedate)
FROM matcherportcoipo
WHERE excludeflag1 = 0 AND excludeflag2 = 0 AND warningflag = 1
GROUP BY file1coname, file1statecode, file1datefirstinv;
--37
Then we can create an include table and union this with the good matches to create a matcher core file for portco and ipos.
CREATE TABLE matcherportcoipoinclude AS
SELECT m.* FROM
matcherportcoipo AS m JOIN matcherportcoipomindate AS mi ON m.file1coname = mi.file1coname AND m.file1statecode = mi.file1statecode AND
m.file1datefirstinv = mi.file1datefirstinv AND m.file2issuedate = mi.min;
--37
And create a matcherportcoipocore table by combining the good matches with the fixed mismatches.
CREATE TABLE matcherportcoipocore AS
SELECT *
FROM matcherportcoipo WHERE excludeflag1 = 0 AND excludeflag2 = 0 AND warningflag = 0
UNION ALL
SELECT *
FROM matcherportcoipoinclude;
--2350
Now verify that the key counts are correct. The number of distinct portco keys should equal the number of rows in the core table.
SELECT COUNT(*) FROM (
SELECT DISTINCT file1coname, file1statecode, file1datefirstinv FROM matcherportcoipocore)a;
--2350
Boom the matcherportcoipocore table is clean and good for use.
==Cleaning geo table==
The geo table contains duplicate keys. The key for the geo table is (coname, city, startyear). Look at the different counts for all keys and distinct keys from the table:
SELECT COUNT(*) FROM (SELECT DISTINCT city, coname, startyear FROM geo)a;
--43651
SELECT COUNT(*) FROM geo;
--43724
SELECT *, COUNT(*)
FROM (SELECT city, coname, startyear FROM geo)a
GROUP BY city, coname, startyear
HAVING COUNT(*) > 1;
If you look at the rows with duplicate keys you can see they are simply complete duplicates so let's create a table with just distinct rows.
DROP TABLE geo1;
CREATE TABLE geo1 AS
SELECT DISTINCT *
FROM geo;
--43662
We still have 11 keys that are not distinct. We'll need to clean those up.
SELECT *, COUNT(*)
FROM (SELECT city, coname, startyear FROM geo1)a
GROUP BY city, coname, startyear
HAVING COUNT(*) > 1;
--8
city | coname | startyear | count
--------------+-----------------------------+-----------+-------
New York | New York Digital Health LLC | 2015 | 2
Portland | Undisclosed Company | 2016 | 2
Hauppauge | Mdeverywhere Inc | 1999 | 2
North Mankato | Angie's Artisan Treats LLC | 2011 | 2
Cincinnati | Undisclosed Company | 2016 | 4
New York | Undisclosed Company | 2015 | 2
San Francisco | Undisclosed Company | 2016 | 2
San Francisco | Undisclosed Company | 2015 | 3
Modify geo1 table query to get rid of Undisclosed Companies:
DROP TABLE geo1;
CREATE TABLE geo1 AS
SELECT DISTINCT *
FROM geo
WHERE coname NOT LIKE '%Undisc%';
--43631
SELECT *, COUNT(*)
FROM (SELECT city, coname, startyear FROM geo1)a
GROUP BY city, coname, startyear
HAVING COUNT(*) > 1;
--3
Now manually check the longitude and latitude of each of these rows and delete one of each of them. Then create your core table and verify that all the keys are distinct.
DELETE FROM geo1 WHERE coname = 'New York Digital Health LLC' AND city = 'New York' AND startyear = 2015 AND lattitude = 44.933143::real AND longitude = 7.540121::real;
--1
DELETE FROM geo1 WHERE coname = 'Mdeverywhere Inc' AND city = 'Hauppauge' AND endyear = 2011;
--1
DELETE FROM geo1 WHERE city = 'North Mankato' AND lattitude = 44.19030721::real AND longitude = -94.052706::real;
--1
CREATE TABLE geocore AS
SELECT *
FROM geo1;
--43628
SELECT COUNT(*) FROM (SELECT DISTINCT city, coname, startyear FROM geocore)a;
--43628
==Name Based Matching geo keys to companybase keys==
Get a list of geokeys and companybasekeys and run them through the [[The Matcher]]. The key is (coname, city, startyear) so you'll need to extract the year from the datefirstinv from the companybasecore table. See below.
DROP TABLE geokeys;
CREATE TABLE geokeys AS
SELECT coname, city, startyear
FROM geocore
WHERE noaddress = 0::boolean;
--33628
\COPY geokeys TO 'geokeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
CREATE TABLE portcokeysforgeo AS
SELECT coname, city, EXTRACT(YEAR FROM datefirstinv)
FROM companybasecore;
--44740
\COPY portcokeysforgeo TO 'portcokeysforgeo.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
After you run the matcher you will notice there are a ton of matching errors as usual. If you simply import this into your vcdb2 and try joining companybasecore with geocore your tables will start to explode. Notice how the line count jumps from 44,740 to 45,018.
DROP TABLE matcherportcogeo;
CREATE TABLE matcherportcogeo (
portcoconame varchar(255),
portcocity varchar(100),
portcostartyear integer,
geoconame varchar(255),
geocity varchar(100),
geodatefirstyear integer
);
\COPY matcherportcogeo FROM 'matcheroutputportcogeo.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--33608
--try matching companybase to geo through the matcherportcogeo
CREATE TABLE companybasecorejoingeo AS
SELECT c.coname, c.statecode, c.datefirstinv, c.investedk, c.city, c.addr1, c.addr2, g.lattitude, g.longitude
FROM companybasecore c
LEFT JOIN matcherportcogeo AS m ON m.portcoconame = c.coname AND m.portcocity = c.city AND m.portcostartyear = EXTRACT(YEAR FROM
c.datefirstinv)
LEFT JOIN geocore AS g ON g.coname = m.geoconame AND m.geocity = g.city AND m.geodatefirstyear = g.startyear;
--45018
Okay so we need to fix this. Luckily I already had this data in another database so I copied it out and imported it into vcdb2. The raw data can be found in a text file in the folder on the Z drive called geolookupold.txt.
CREATE TABLE geoimport (
coname varchar(100),
statecode varchar(2),
datefirstinv date,
latitude real,
longitude real
);
\COPY geoimport FROM 'geolookupold.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--42678
SELECT COUNT(*) FROM
(SELECT DISTINCT coname, statecode, datefirstinv FROM geoimport)a;
--42678
CREATE TABLE companybasegeomaster AS
SELECT c.coname, c.statecode, c.datefirstinv, c.investedk, c.city, c.addr1, c.addr2, g.latitude, g.longitude
FROM companybasecore AS c
LEFT JOIN geoimport AS g ON c.coname = g.coname AND c.statecode = g.statecode AND c.datefirstinv = g.datefirstinv;
--44740
\COPY companybasegeomaster TO 'companybasegeomaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Gathering geo data from company addresses==
If you do not already have a file with all the geo data in it you can lookup the latitude, longitude data from google using the company address. A link on how to use the [[Geocode.py]] is found here. When you copy the addresses out of the database be sure to include a distinct key that will allow you to join the geo data back with the portco key. Some of the geo coordinates are incorrect. This was found while analyzing the output data. I traced this back to a dirty file we initially used for geo coordinates called GeocodedVCData. In the future the safest way to get geo-coordinates is to use the Geocode.py script by feeding company addresses.
==Build dead/alive flags==
First find the deaddate for each company. Make sure that you have companybasecore, ipocore, mascore tables. Then calculate a deaddate. If there is no exit then the deaddate is datelastinv + 5 years. Take a look at the queries below.
CREATE TABLE deaddate AS
SELECT c.coname, c.statecode, c.datefirstinv, c.datelastinv, i.issuedate, m.announceddate
FROM companybasecore AS c
LEFT JOIN companybasekeyipokeycore AS ipokey ON c.coname = ipokey.coname AND c.statecode = ipokey.statecode AND c.datefirstinv =
ipokey.datefirstinv
LEFT JOIN companybasekeymaskeycore AS maskey ON c.coname = maskey.coname AND c.statecode = maskey.statecode AND c.datefirstinv =
maskey.datefirstinv
LEFT JOIN ipocore AS i ON i.issuer = ipokey.ipoissuer AND i.issuedate = ipokey.ipoissuedate AND i.statecode = ipokey.ipostatecode
LEFT JOIN mascore AS m ON m.targetname = maskey.mastargetname AND m.targetstatecode = maskey.masstatecode AND m.announceddate =
maskey.announceddate;
--44740
CREATE TABLE deaddate1 AS
SELECT *,
CASE
WHEN issuedate IS NULL AND announceddate IS NULL THEN datelastinv + INTERVAL '5 YEAR'
WHEN issuedate IS NOT NULL THEN issuedate
WHEN announceddate IS NOT NULL THEN announceddate
END AS deaddate
FROM deaddate;
--44740
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
'''NOTES FROM ED:'''
*There wasn't a full population deadalive portco level keyed table, so I made one
DROP TABLE PortCoDeadAliveMaster;
CREATE TABLE PortCoDeadAliveMaster as
SELECT coname, city, statecode, datefirstinv, datelastinv, deaddate, extract(year from datefirstinv) as aliveyear,
extract(year from deaddate) AS deadyear
FROM deadalive;
--44740
This code is in '''FixingVCDB2.sql''' in Z:\VentureCapitalData\SDCVCData\vcdb2
==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;
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 colevelsimple==
DROP TABLE colevelsimple;
CREATE TABLE colevelsimple AS
SELECT coname, statecode, datefirstinv, city, addr1, addr2, zip, aliveyear, deadyear, latitude, longitude
FROM coleveloutput WHERE aliveyear IS NOT NULL and deadyear IS NOT NULL AND latitude IS NOT NULL;
--31171
==Creating roundplus==
DROP TABLE roundplus;
CREATE TABLE roundplus AS
SELECT roundcore.*, c.city, 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
LEFT JOIN companybasecore AS c ON c.coname = roundcore.coname AND c.statecode = roundcore.statecode AND c.datefirstinv =
roundcore.datefirstinv;
--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
==Creating round level outputs==
roundplus is used to build the two table outputs below at the round level.
DROP TABLE roundleveloutput;
CREATE TABLE roundleveloutput AS
SELECT city, statecode, roundyear as year,
sum(roundamtm*seedflag) AS seedamtm,
sum(roundamtm*earlyflag) AS earlyamtm,
sum(roundamtm*laterflag) AS lateramtm,
sum(roundamtm*growthflag) AS selamtm,
sum(seedflag) AS numseeds,
sum(earlyflag) AS numearly,
sum(laterflag) AS numlater,
sum(growthflag) AS numsel,
sum(dealflag) AS numdeals
FROM roundplus WHERE hadgrowthvc=1 GROUP BY city, statecode, roundyear ORDER BY city, statecode, roundyear;
--22266
DROP TABLE roundleveloutput2;
CREATE TABLE roundleveloutput2 AS
SELECT roundleveloutput.*, numalive
FROM roundleveloutput
LEFT JOIN alivecount ON alivecount.city=roundleveloutput.city AND alivecount.statecode=roundleveloutput.statecode AND
alivecount.year=roundleveloutput.year;
--22266
==Cleaning round table==
Use coname, rounddate as the key for this table. Exclude all keys that occur more than once.
CREATE TABLE roundexclude AS
SELECT * FROM (
SELECT coname, rounddate FROM round) t
GROUP BY coname, rounddate
HAVING COUNT(*) > 1;
--154
CREATE TABLE roundcore AS
SELECT * FROM round
WHERE NOT EXISTS (SELECT * FROM roundexclude AS re WHERE re.coname = round.coname AND re.rounddate = round.rounddate);
--143000
==Creating Stage Flags Table==
FROM round;
'''NOTES FROM ED'''*There wasn't a colevel, portco keyed master flag table, so I made one from '''roundplus''' (see below)*I also included basic round summary data DROP TABLE PortCoFlagMaster; CREATE TABLE PortCoFlagMaster AS SELECT coname, statecode, datefirstinv, city max(growthflag) AS hadgrowth, sum(growthflag) as numgrowth, max(seedflag) AS hadseed, sum(seedflag) as numseed, max(earlyflag) AS hadearly, sum(earlyflag) as numearly, max(laterflag) AS hadlater, sum(laterflag) as numlater, max(transactionflag) AS hadtrans, sum(transactionflag) as numtrans, max(excludeflag) AS hadexcl, sum(excludeflag) as numexcl, max(dealflag) AS haddeal, COUNT(rounddate) as numrounds, sum(numinvestors) as totalinvestors FROM roundplus GROUP BY coname, statecode, datefirstinv, city; --44721 Code is in FixingVCDB2.sql in Z:\VentureCapitalData\SDCVCData\vcdb2 ==Fixing erroneous geo-coordinates==Some of the geocoordinates in the db are dirty and point to locations in India, Eastern Europe. However, the company addresses exist. Isolate the dirty geo-coordinates and do a lookup using Geocode.py script. To isolate place a box around the continental US and flag all points that fall outside the box. Add back the points that are located in Hawaii and Puerto Rico. Then import back into db. I used longitude boundaries of -66 to -125 and latitude boundaries of 24 to 50. --identify bad geo coords DROP TABLE badgeodata; CREATE TABLE badgeodata ( city varchar(100), companyname varchar(100), startyear real, endyear real, latitude real, longitude real, noaddress int ); \COPY badgeodata FROM 'badgeodata.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --43724 DROP TABLE geodirtydata; CREATE TABLE geodirtydata AS SELECT g.* FROM geoimport AS g INNER JOIN badgeodata AS bg ON g.coname =Cleaning bg.companyname; --30498 \COPY geodirtydata TO 'geodirtydata.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV DROP TABLE geodirtydatawithflags; CREATE TABLE geodirtydatawithflags ( coname varchar(100), statecode varchar(2), datefirstinv date, latitude real, longitude real, longdirtyflag int, latdirtyflag int, hawaiiflag int, prflag int, latlongflag int, masterflag int ); \COPY geodirtydatawithflags FROM 'geodirtydataflags.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --30498 --import coordinates back into db DROP TABLE geodirtyfix; CREATE TABLE geodirtyfix ( coname varchar(100), statecode varchar(2), datefirstinv date, latitude real, longitude real ); \COPY geodirtyfix FROM 'geodirtyfix.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --2300 DROP TABLE geoimportclean; CREATE TABLE geoimportclean AS SELECT g.* FROM geoimport AS g WHERE g.coname NOT IN (SELECT coname FROM geodirtyfix); --40378 --42678 DROP TABLE geoimportfix; CREATE TABLE geoimportfix AS SELECT * FROM geoimportclean UNION ALL SELECT * FROM geodirtyfix WHERE latitude IS NOT NULL; --41718Then redo coleveloutput and colevelsimple using the Companybase geoimportfix as your geo tableinstead of geoimport. ==Re-Fixing erroneous geo-coordinates==Every table There are still geo errors in the db. Addresses within the US have incorrect geo-coordinates. To fix this problem we will just lookup all the addresses in the DB using the Geocode.py script. Also we need to pull a company level file from SDC because the addresses will be copied down or be null by the normalizer. Modify your round ssh sdc script to remove the round dates. Therefore only one line will be assigned to one company. There will contain some duplicate keys be no normalization errors this way. Then copy into the db and copy out all the distinct coname, statecode, datefirstinv that have a value in addr1 or addr2. Then run this through the geocode script. Copy the result back into the db and erroneous entriesredo the colevel output tables. We '''re going NOTES FROM ED: *The lat/long data has to be stored as decimal or numeric to prevent precision problems. *The join was to clean the companybase old (wrong) base table so , that every key had addresses copied down. *The code below has been fixed up and rerun. The fixed up code is in ProcessingCoLevelSimple.sql DROP TABLE geoallcoords; CREATE TABLE geoallcoords ( conamevarchar(100), statecodevarchar(2), datefirstinvdate, latitude numeric, longitude numeric ) ; \COPY geoallcoords FROM 'sdccompanygeolookup.txt_coords' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --44999 --select latitude, longitude from geoallcoords; --redo the colevel output tables DROP TABLE coleveloutput; CREATE TABLE coleveloutput AS SELECT A.coname, A.statecode, A.datefirstinv, A.city, A.addr1, A.addr2, A.zip, g.latitude, g.longitude, d.deaddate, d.aliveyear, d.deadyear FROM sdccompanybasecore2 AS A LEFT JOIN deadalive1 AS d ON d.coname=A.coname AND d.statecode=A.statecode AND d.datefirstinv=A.datefirstinv LEFT JOIN geoallcoords AS g ON g.coname = A.coname AND g.statecode = A.statecode AND g.datefirstinv = A.datefirstinv WHERE hadgrowthvc=1; --32380 DROP TABLE colevelsimple; CREATE TABLE colevelsimple AS SELECT coname, statecode, datefirstinv, city, addr1, addr2, zip, aliveyear, deadyear, latitude, longitude FROM coleveloutput WHERE aliveyear IS NOT NULL and deadyear IS NOT NULL AND latitude IS NOT NULL; --31523 <nowiki>\COPY colevelsimple TO 'colevelsimple.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV</nowiki> '''NOTE FROM ED:'''*The query below is now redundant. *The same cleaning is uniquedone in ProcessingCoLevelSimple. sql*The clean table is CoLevelSimpleClean*This means clean table is used as the basis for CoLevelBlowOut I also added flags on the geodata table to filter points outside the US. You can use the geoallcoords1 table instead of geoallcoords and set excludeflag = 1 to filter out 292 erroneous points when you create your colevel tables. CREATE TABLE geoallcoords1 AS SELECT *, CASE WHEN longitude < -125 OR longitude > -66 OR latitude < 24 OR latitude > 50 OR latitude = NULL OR longitude = NULL THEN 1::int ELSE 0::int END AS excludeflag FROM geoallcoords; --44999 SELECT COUNT(*) FROM geoallcoords1 WHERE excludeflag = 1; --292 ==Cleaning firmbase== NOTE FROM MEGHANA: *The base table - firmbase - was reloaded after it was found to be incorrectly normalized.*[[Retrieving_US_VC_Data_From_SDC#VC_Funds]] was corrected*The code below has been rerun! First flag the undisclosed funds. The firmbase table contains undisclosed firms. Add a flag and remove them. Then use firmname, statecode, foundingdate as the key for this table. Check that is valid and make your core table. DROP TABLE firmbase1; CREATE TABLE firmbase1 AS SELECT *, CASE WHEN firmname LIKE '%Undisclosed Firm%' THEN 1::int ELSE 0::int END AS undisclosedflag FROM firmbase; --9734 SELECT COUNT(*) FROM firmbase1 WHERE undisclosedflag = 0; --9452 SELECT COUNT(*) FROM (SELECT DISTINCT firmname, statecode, foundingdate FROM firmbase1 WHERE undisclosedflag = 0)a; --9452 DROP TABLE firmbasecore; CREATE TABLE firmbasecore AS SELECT * FROM firmbase1 WHERE undisclosedflag = 0; --9452 Instead we chose to use only firmname as the key because there will be were not too many duplicates. We remove the duplicates by selecting the lesser foundingdate. DROP TABLE firmbaseduplicates; CREATE TABLE firmbaseduplicates AS SELECT *, COUNT(*) FROM (SELECT firmname FROM firmbase1 WHERE undisclosedflag = 0)a GROUP BY firmname HAVING COUNT(*) > 1; --6 DROP TABLE firmbaseinclude; CREATE TABLE firmbaseinclude AS SELECT f.firmname, MAX(f.foundingdate) AS foundingdate FROM firmbase1 AS f INNER JOIN firmbaseduplicates AS d ON f.firmname = d.firmname GROUP BY f.firmname; --6 DROP TABLE firmbasec; CREATE TABLE firmbasec AS SELECT l.* FROM firmbase1 AS l LEFT JOIN firmbaseinclude AS r ON r.firmname = l.firmname AND r.foundingdate = l.foundingdate WHERE r.firmname IS NULL AND undisclosedflag = 0; --9446 DROP TABLE firmbasecore; CREATE TABLE firmbasecore AS SELECT * FROM firmbasec WHERE firmname NOT LIKE 'Amundi%'; SELECT COUNT(DISTINCT firmname) FROM firmbasecore; --9445 NOTE:We have dropped 'Amundi' for reasons specified in "Cleaning branchoffices," below. ==Cleaning branchoffices== DROP TABLE branchofficesbase; CREATE TABLE branchofficesbase AS SELECT *, CASE WHEN firmname LIKE '%Undisclosed Firm%' THEN 1 relationship between ::int ELSE 0::int END AS undisclosedflag FROM branchoffices; --9734 SELECT COUNT(*) FROM branchofficesbase WHERE undisclosedflag = 0; --9452 DROP TABLE branchofficecore; CREATE TABLE branchofficecore AS SELECT * FROM branchofficesbase WHERE undisclosedflag = 0; --9452 DROP TABLE bocore; CREATE TABLE bocore AS SELECT * FROM branchofficecore WHERE firmname not like 'Amundi%'; --9450 SELECT COUNT(*) FROM (SELECT DISTINCT firmname FROM bocore WHERE bocity IS NOT NULL) AS T; --1962 There are no duplicates in firmbasecore. In firmbase, 'Amundi Private Equity Funds SA' is the only duplicate, but we dropped the one with the earlier founding date when we cleaned firmbase. Amundi has 2 branch offices in the same city (Bucharest) but different zip codes, which is why there are different counts when we use firmname/firmname,bocity vs firmname, bozip as our key. NOTE: We have dropped "Amundi" from firmbasecore, as well as branchofficecore, in order to use firmname as a valid key for bocore. ==Cleaning fundbase== NOTE FROM ED:*The base table - '''fundbase''' - was reloaded after it was found to be incorrectly normalized. **All sorts of fields were copied down when they shouldn't have been.*[[Retrieving_US_VC_Data_From_SDC#VC_Funds]] was corrected*The code in this section has been rerun. First flag the undisclosed funds. DROP TABLE fundbase1; CREATE TABLE fundbase1 AS SELECT *, CASE WHEN fundname LIKE '%Undisclosed Fund%' THEN 1 ::int ELSE 0::int END AS undisclosedflag FROM fundbase; --27588 SELECT COUNT(*) FROM fundbase1 WHERE undisclosedflag = 0; --27097 SELECT COUNT(*) FROM (SELECT DISTINCT fundname, firstinvdate FROM fundbase1 WHERE undisclosedflag = 0)a; --27097 You can see that fundname, firstinvdate is a good key and 1 entry. Given an entry you But we're going to use simply the fundname as a key because it will be able easier to create do join operations later. DROP TABLE fundbasecore; CREATE TABLE fundbasecore AS SELECT * FROM fundbase1 WHERE undisclosedflag = 0; --27097 SELECT COUNT(*) FROM (SELECT DISTINCT fundname FROM fundbase1 WHERE undisclosedflag = 0)a unique key ; --27050 The plan is to grab all the duplicate fundnames and given only include the ones with the MIN(closedate) AND MIN(lastinvdate) in the fundbasecore table. DROP TABLE fundnameexclude; CREATE TABLE fundnameexclude AS SELECT fundname, COUNT(*) FROM (SELECT fundname FROM fundbase1 WHERE undisclosedflag = 0)a coname GROUP BY fundname HAVING COUNT(*) > 1; --47 DROP TABLE fundexclude; CREATE TABLE fundexclude AS SELECT f.* FROM fundbase1 AS f INNER JOIN fundnameexclude as e ON f.fundname = e.fundname; --94 DROP TABLE fundbase2; CREATE TABLE fundbase2 AS SELECT * FROM fundbase1 WHERE undisclosedflag = 0 EXCEPT SELECT * FROM fundexclude; --27003 DROP TABLE fundinclude; CREATE TABLE fundinclude AS SELECT fundname, MIN(closedate) AS closedate, MIN(lastinvdate) AS lastinvdate FROM fundexclude GROUP BY fundname; --47 DROP TABLE fundinclude2; CREATE TABLE fundinclude2 AS SELECT f.* FROM fundbase1 AS f INNER JOIN fundinclude AS fu ON f.fundname = fu.fundname AND f.closedate = fu.closedate AND f.lastinvdate = fu.lastinvdate; --17 --create fundcore table DROP TABLE fundbasecore; CREATE TABLE fundbasecore AS SELECT * FROM fundbase2 UNION ALL SELECT * FROM fundinclude2; --27020 ==Name based matching firms to funds==Get the firms and fund keys and also include the firmname from the fundbasecore table. Run these two files through the Matcher. Then manually flag the multiple matches. There are only ~50 of them. Then reimport to vcdb2. DROP TABLE fundkeysandfirms; CREATE TABLE fundkeysandfirms AS SELECT fundname, firstinvdate, firmname FROM fundbasecore; --27020 \COPY fundkeysandfirms TO 'fundkeysandfirms.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV DROP TABLE firmkeys; CREATE TABLE firmkeys AS SELECT firmname, statecode, datefirstinv key foundingdate FROM firmbasecore; \COPY firmkeys TO 'firmkeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --9446 CREATE TABLE matcherfirmsfunds ( firmname varchar(100), firmstatecode varchar(2), firmfoundingdate date, fundname varchar(100), fundfirstinvdate date, fundfirmname varchar(100), excludeflag int, excludeflagmaster int ); \COPY matcherfirmsfunds FROM 'matcheroutputfundsfirms.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --2364 ==Joining firms with funds== DROP TABLE firmfundstestjoin; CREATE TABLE firmfundstestjoin AS SELECT f.firmname AS firmfirmname, fu.firmname AS fundsfirmname FROM firmbasecore AS f INNER JOIN fundbasecore AS fu ON f.firmname = fu.firmname WHERE fu.firmname != 'Undisclosed Firm';If you do the full join you will be able notice that there are 30 firms in the funds table that do not exist in the firms table. ==Joining funds with roundline==There is a lot of mismatches between funds and roundline. After investigation, it seems that some of the fundnames were altered in the roundline data. We will need to fix the RoundOnOneLine.pl and Normalize.pl scripts to find exactly fix this issue. After fixing and reimporting the roundline into vcdb2 there are still 1 entry ,963 funds that appear in roundline that are not in fundbasecore. SELECT COUNT(*) FROM (SELECT a.fundname, b.fundname FROM (SELECT DISTINCT fundname FROM roundline1 WHERE undisclosedflag=0) AS A LEFT JOIN (select distinct fundname FROM fundbasecore) AS B ON a.fundname=b.fundname WHERE b.fundname IS NULL)as r; --1963 --Look at the key corresponds to ones that don't match SELECT a.fundname, b.fundname FROM (SELECT DISTINCT fundname FROM roundline1 WHERE undisclosedflag=0) AS A LEFT JOIN (select distinct fundname FROM fundbasecore) AS B ON a.fundname=b.fundname WHERE b.fundname IS NULL; SELECT COUNT(DISTINCT fundname) FROM roundline1 WHERE undisclosedflag=0; --19091 SELECT count(*) FROM ((SELECT a.fundname, b.fundname FROM (SELECT DISTINCT fundname FROM roundline1 WHERE undisclosedflag=0) AS A JOIN (select distinct fundname FROM fundbasecore) AS B ON a.fundname=b.fundname)) AS t; --17128 SELECT COUNT(DISTINCT fundname) FROM fundbasecore; --27044 SELECT count(*) FROM ((SELECT a.fundname, b.firmname FROM (SELECT DISTINCT fundname, firmname FROM fundbasecore) AS A JOIN (select distinct firmname FROM firmbasecore) AS B ON a.firmname=b.firmname)) AS t; --26910 SELECT COUNT(DISTINCT firmname) FROM fundbasecore; --14103 SELECT count(*) FROM ((SELECT a.firmname, b.firmname FROM (SELECT DISTINCT firmname FROM firmbasecore) AS A JOIN (select distinct firmname FROM fundbasecore) AS B ON a.firmname=b.firmname)) AS t; --14084 ==Creating portcoexitmaster==Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first. Note that the amounts are actually in m (not k) in the companybase input. ALTER TABLE portcoexitbuild RENAME TO portcoexitbuildbak; CREATE TABLE portcoexitbuild AS SELECT coname, statecode, datefirstinv, ipoissuedate, masannounceddate, ipoamt, maamt, investedk FROM companybaseipomasmaster; --44740 DROP TABLE portcoexitmaster; CREATE TABLE portcoexitmaster AS SELECT coname, statecode, datefirstinv, investedk, CASE WHEN ipoissuedate IS NOT NULL THEN ipoissuedate WHEN masannounceddate IS NOT NULL THEN masannounceddate ELSE NULL END AS exitdate, CASE WHEN ipoissuedate IS NOT NULL THEN 1::int ELSE 0::int END AS ipoflag, CASE WHEN masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS maflag, CASE WHEN ipoissuedate IS NOT NULL OR masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS exitflag, CASE WHEN ipoissuedate IS NOT NULL THEN ipoamt::numeric::float8 ELSE maamt::numeric::float8 END AS exitvaluem FROM portcoexitbuild; \COPY portcoexitmaster TO 'portcoexitmaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV ==Creating portcoroundlinefirmmaster==portcoroundlinefirmmaster table setcontains portcokey, roundline, firm table. CREATE TABLE portcoroundlinefirmmaster AS SELECT c.coname, c.statecode, c.datefirstinv, r.rounddate, r.amountk, r.fundname, f.firmname FROM companybasecore AS c INNER JOIN roundline AS r ON c.coname = r.coname AND c.statecode = r.statecode AND c.datefirstinv = r.datefirstinv INNER JOIN fundbasecore AS fu ON fu.fundname = r.fundname INNER JOIN firmbasecore AS f ON f.firmname = fu.firmname; --299321 ==Joining funds, firms, roundline with companybasecore== DROP TABLE fundbasefirmbaseroundlinegoodkeys; CREATE TABLE fundbasefirmbaseroundlinegoodkeys AS SELECT c.coname, c.statecode, c.datefirstinv, r.coname AS rconame, r.statecode AS rstatecode, r.datefirstinv AS rdatefirstinv, fu.fundname, f.firmname, f.location FROM companybasecore AS c INNER JOIN roundline1 AS r ON c.coname = r.coname AND c.statecode = r.statecode AND c.datefirstinv = r.datefirstinv INNER JOIN fundbasecore AS fu ON fu.fundname = r.fundname INNER JOIN firmbasecore AS f ON f.firmname = fu.firmname WHERE fu.firmname != 'Undisclosed Firm'; --298688Now count the distinct firms, funds and portcokeys SELECT COUNT(DISTINCT firmname) FROM fundbasefirmbaseroundlinegoodkeys; --8956 SELECT COUNT(DISTINCT fundname) FROM fundbasefirmbaseroundlinegoodkeys; --16907 SELECT COUNT(*) FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM fundbasefirmbaseroundlinegoodkeys) as gfoo; --42093You can see that there are many keys that do not exist in the other datasets.
SELECT COUNT(*)
FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybasesdccompanybase)a; --4477444996
SELECT COUNT(*)
FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybasesdccompanybase)a; --4477144997You can see that they key is not unique because the counts don't match up. There are 44,771 distinct keys but there are 44,774 keys in the companybase table. So 1 key can match to more than one entry in the table.Some of the data in the companybase table contains undisclosed company names and companies that exist in other countries outside the US. So let's build flags for these two events and check the key count again. DROP TABLE companybase1sdccompanybase1; CREATE TABLE companybase1 sdccompanybase1 AS
SELECT *,
CASE
ELSE 0::int
END AS undisclosedflag
FROM companybasesdccompanybase; --44997 SELECT COUNT(*) FROM (SELECT coname, statecode, datefirstinv FROM sdccompanybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0)a; --44966 SELECT COUNT(*) FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM sdccompanybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0)a; --44966 DROP TABLE sdccompanybasecore; CREATE TABLE sdccompanybasecore AS SELECT DISTINCT coname,lastupdated,foundingdate,datelastinv,datefirstinv,investedk,city,description,msa,msacode,nationcode,statecode,addr1,addr2,indcla ss,indsubgroup,indminorgroup,url,zip FROM sdccompanybase1 WHERE nationcode = 'US' AND undisclosedflag=0; --44966
SELECT COUNT(*)
FROM (SELECT coname, statecode, datefirstinv FROM companybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0sdccompanybasecore)a; --44771
SELECT COUNT(*)
FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybase1 sdccompanybasecore)a; DROP TABLE roundplus; CREATE TABLE roundplus AS SELECT roundcore.*, c.city, 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 LEFT JOIN sdccompanybasecore AS c ON c.coname = roundcore.coname AND c.statecode = roundcore.statecode AND c.datefirstinv = roundcore.datefirstinv; --142999 DROP TABLE roundleveloutput; CREATE TABLE roundleveloutput AS SELECT city, statecode, roundyear as year, sum(roundamtm*seedflag) AS seedamtm, sum(roundamtm*earlyflag) AS earlyamtm, sum(roundamtm*laterflag) AS lateramtm, sum(roundamtm*growthflag) AS selamtm, sum(seedflag) AS numseeds, sum(earlyflag) AS numearly, sum(laterflag) AS numlater, sum(growthflag) AS numsel, sum(dealflag) AS numdeals FROM roundplus WHERE alwaysusflag hadgrowthvc= 1 GROUP BY city, statecode, roundyear ORDER BY city, statecode, roundyear; --22374 DROP TABLE roundleveloutput2; CREATE TABLE roundleveloutput2 AS SELECT roundleveloutput.*, numalive FROM roundleveloutput LEFT JOIN alivecount ON alivecount.city=roundleveloutput.city AND undisclosedflag alivecount.statecode=roundleveloutput.statecode AND alivecount.year= 0)aroundleveloutput.year; --4477022374By looking at the counts you can see that there is still 1 duplicate key in the \COPY roundleveloutput2 TO 'roundleveloutput2.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV ===Creating a PortCoMaster Table=== Using '''sdccompanybasecore2''' as a base table, Ed created a useful PortCoMasterTable. LetThis table isn's find it another wayt perfect:*sdccompanybasecore2 was drawn later and has 44966. Running the query below finds the key It has fixed addresses (without copy down mistakes)*The other tables - PortCoDeadAliveMaster, PortCoExitMaster, and PortCoFlagMaster - are built off slightly earlier draws and so have 44740 records-The other tables are all keyed by coname, statecode, and datefirstinv) that appears twice in the table. DROP TABLE PortCoMaster; CREATE TABLE PortCoMaster AS SELECT A.*, B.deaddate, B.aliveyear, B.deadyear, C.ipoflag, C.maflag, C.exitflag, C.exitvaluem, D.hadgrowth, D.numgrowth, D.hadseed, D.numseed, D.hadearly, D.numearly, D.hadlater, D.numlater, D.hadtrans, D.numtrans, D.hadexcl, D.numexcl, D.haddeal, D.numrounds, D.totalinvestors FROM (SELECT sdccompanybasecore2 AS A LEFT JOIN PortCoDeadAliveMaster AS B ON A.coname=B.coname, AND A.statecode, datefirstinv FROM companybase1 WHERE alwaysusflag = 1 B.statecode AND undisclosedflag A.datefirstinv= 0)AS keyB.datefirstinv GROUP BY LEFT JOIN PortCoExitMaster AS C ON A.coname=C.coname, AND A.statecode=C.statecode, AND A.datefirstinv=C.datefirstinv HAVING COUNT(key) > 1;The output looks like this: LEFT JOIN PortCoFlagMaster AS D ON A.coname=D.coname | AND A.statecode | =D.statecode AND A.datefirstinv=D.datefirstinv; ----------------------------+-----------+--------------44966 \COPY PortCoMaster TO 'PortCoMaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV New York Digital Health LLC | NY | 2015-08-1344966We'll have to copy companybase1 out of the db and have ==Cleaning roundline==Add a look on textpad flag for something unique about one of the entries on New York Digital Health LLC that we can use to manually delete it from the companybase1 tableundisclosed funds. Turns out the url is different so we DROP TABLE roundline1; CREATE TABLE roundline1 AS SELECT *, CASE WHEN fundname = 'Undisclosed Fund'll use that.THEN 1::int ELSE 0::int END AS undisclosedflag FROM roundline; --385753
