158
edits
Changes
Jump to navigation
Jump to search
no edit summary
==Creating People Tables==
We pulled data on executives in both portcos and funds. I describe the process below. If any of the explanations don't make sense, I also describe most tables in the section called Marcos's Code. This SQL file is located in: E:McNair\Projects\VentureXpert Database\vcdb3\LoadingScripts\MatchingEntrepsV3
===Company People===
DROP TABLE titlelookup;
-built from masteronesynth, masterreals
-combines the real and one synth table
==Ranking Tables and Graphs==
This is a slight detour from the creation of VCDB3. However, this is a cool process because you actually get to use the data you've been working with. This process is extensive, but the queries are easy to understand. If you wish to have deeper understanding of the process, read the code. It is located in:
E:McNair\Projects\VentureXpert Database\vcdb3\LoadingScripts\RoundRanking
First you must create a table that has aggregate round information grouped by cities and round year. Since this is a little difficult to picture, I will attach the code.
DROP TABLE roundleveloutput;
CREATE TABLE roundleveloutput AS SELECT
city, statecode, roundyear AS year,
SUM(rndamtestm*seedflag) AS seedamnt,
SUM(rndamtestm*earlyflag) AS earlyamnt,
SUM(rndamtestm*laterflag) AS lateramnt,
SUM(rndamtestm*growthflag) AS selamnt,
SUM(growthflag*dealflag) AS numseldeals
FROM round GROUP BY city, statecode, roundyear;
--30028
Next create a table that lists the all time SEL amount by city. Keep including the state code since this will ensure that you have the right city. City names are often repeated in different states. Next, create a table which lists unique city, state for every year since 1980. Then, build a table which matches portcos to the city, state, year blowout table for each year they were alive. This table should be relatively large since it lists companies once for every year they were alive up until the present. Then create a table that displays the number of companies alive in a city every year since 1980. Then add in a table that lists all of the information you have built in tables previously based on city, state, year. Also add in population. Then you can run the ranking queries.
For states follow the same general process but group by states not cities and states.
If this explanation was not enough for you (it was not meant to be in depth) go to the location defined above and read the actual code. With the description I have given, you should be able to piece together what each query does.
==Master Tables==
