158
edits
Changes
Jump to navigation
Jump to search
no edit summary
SELECT A.*, B.latitude AS BOLatitude, B.longitude AS BOLongitude FROM firmgeocore AS A LEFT JOIN bogeocore1 AS B ON A.firmname=B.firmname;
--15437
==Marcos's Code==
===Describing Stacks Created in Code===
CoPeopleBase:
-Builds from copeople and titlelookup
-Identifies what roles people played in their companies
namegender:
-built from copeoplebase
-identifies male/female/unknown
CoPeopleFull:
-built from copeoplebase and namegender
-builds more extensive information on executive including speficially what level of executive they are
CoPeopleKey:
-built from CoPeopleFull
-creates table where only executives with full primary keys are kept
CoPeopleSerial:
-built from copeoplekey
-keeps track of executives previous jobs at executive level
CoPoepleAgg:
-built from copeoplekey and copeopleserial
-gets extensive information on executives for each company
FundPeopleBae:
-built from fundpeople
-identifies male/female/doctor
-hasperson column slightly weird because we can only have the lastname without prefix or first name and still have a 1 in column. Seems to be of little use/too broad
FundPeopleFull:
-built from fundpeoplebase, namegender
-adds in male/female
fundpeopleagg:
-built from fundpeoplefull
-has aggregations of gender info for each fund
RoundLineJoinerLeanffWlistno:
-built from rounlinejoinerleanff
-adds listno to funds
RoundLineAggFunds:
-built from roundlinejoinerleanffwlistno and rounlineaggfirms
-if there are two funds from one firm that invest in same portco, we choose only one and leave the others behind
RoundLineAggWExit:
-built from roundlineaggfirms, portcoexitupdated, roundlineaggfunds
-adds in exit information for each company in roundlineaggfirms
FirmPerf:
-built from roundlineaggwexit
-adds in various performance measures for a given firm
PortCoFundDemo:
-built from roundlinejoinerleanffclean and fundpeopleagg
-gives information on executives of funds who invested in the portcos
PortCoPeopleMaster:
-built from PortCoMaster, PortCoIndustry, PortCoPatent, PortCoSBIR, copeoplagg, PortCoFundDemo, CPI, statelookupint
-huge amount of data about companies and their executives
RoundAggDistBase:
-built from portcogeo, firmbogeo, roundlineaggwexit
-creates geographic points using long, lat from geocoding
RoundAggDist:
-Built from roundaggdistbase
-gets actual distances between portcos and firms. if branch office exists and distance is less than distance to firm chooses that also generates random number
FirmPeopleAgg:
-built from roundlineaggfunds, fundpeopleagg
-finds information on executives from different firms
PortCoMatchmaster:
-built from portcopatent, porcoindustry, portcosbir, copeopleaggsimple, portcoid
-gets all information together about portcos
FirmMatchMaster:
-built from firmperf, firmvars, firmpeopleagg, firmid
-gets all information together about firms
RoundLineMasterBase:
-built from portcomatchmaster, firmmatchmaster, roundaggdist, roundlineaggwexit
-builds large amount of information about portcos and firms spceifically info about exits and distances
MatchMostNumerous:
-built from roundlinemasterbase
-finds max number of portcos invested in by a firm that also invested in the company grouping by
MatchHighestRandom:
-built from matchmostnumerous
-if two firms that invested in one company had the same number of max port cos this randomly chooses one company
FirmActiveYearsCode20:
-built from roundlinejoinerleanffclean, porcoindustry
-adds firmname to industry code not exactly sure why distinct is used in query
RealMatchesCode20:
-built from MatchHighestRandom, PortCoIndustry
-real matches between portcos and firms that invested in them including the code20
SyntheticFirmSetBaseCode20:
-built from realmatchescode20, firmactiveyarscode20
-crossproduct of firms and portcos. finds firms that invested in same year as portco received first inv, firms invested in same type of company, and makes sure matches are unique
AllMatchKeys:
-built from SyntheticFirmSetBaseCode20, RealMatchesCode20
-combines synthetic and real matches
SynthRoundAggDistBaseCode20:
-built from allmatchkeys, portcogeo, firmbogeo
-builds points for all portco, firm listings in allmatch keys
SynthRoundAddDistCode20:
-built from synthroundaggdistvasecode20
-finds actual distance between portcos and firms using installed extensions chooses branch offices if distance between portco and bo less than firm
SynthFirmnameInduBlowoutCode20:
-built from allmatchkeys, roundlinemasterbase
-gets every firm combination and checks whehter the companies that those firms invested in are in the same general industry
SynthFirmNameroundInduHistCode20:
-built from SynthFirmnameInduBlowoutcode20
-gets information by portco, firmname match about what the firms past investment patterns are
MasterWithSynthBaseCode20Portco:
-built from Allmatchkeys, matchhighestrandom, synthroundaggdistcode20, sythnfirmnameroundinduhistcode20, synthfirmnameroundindutotalcode20, firmvars, copeopleaggsimple, portcomaster
-builds a bunch of information about synthetic and real matches
SynthFirmnameRoundInduTotalCode20:
-built from allmatchkeys, roundlinemasterbase
-finds number of portcos in certain industries by firmnames
MasterWithSynthCode20Firms:
-built with firmmatchmaster, allmatchkeys
-matching a bunch of information to all firms
MasterWithSynthcode20:
-built from masterwithsynthbasecode20portco, masterwithsynthcode20firms
-gets a huge amount of info together on real and synthetic matches about firms and companies
MasterReals:
-built from masterwithsynthcode20
-gets just real matches from code
MasterOneSynth:
-built from masterwithsynthcode20
-gets just one randomly chosen synthetic match between companies and firms
MasterRealOneSynth:
-built from masteronesynth, masterreals
-combines the real and one synth table
