futoase · March 1, 2016 05:19
diff --git a/0-Searchable list of companies with employees on Hacker News.md b/0-Searchable list of companies with employees on Hacker News.md
diff --git a/1-companies-and-authors-on-hacker-news.csv b/1-companies-and-authors-on-hacker-news.csv
diff --git a/2-companies-and-authors-on-hacker-news.sql b/2-companies-and-authors-on-hacker-news.sql
 SELECT
  company,
  author,
  url
 FROM
  extractCompany(
  SELECT
    author,
    text,
    id
  FROM
    [fh-bigquery:hackernews.comments]
  WHERE
    text LIKE "%I work at %")
 WHERE
  LENGTH(company) > 3
 ORDER BY
  company
diff --git a/3-extractCompanyExample.js b/3-extractCompanyExample.js
 function extractCompanyExample(row, emit) {
  var company = (/I work at ([a-zA-Z]+)/.exec(row.text) || [])[1];
  var url = "https://news.ycombinator.com/item?id=" + row.id.toString();
  emit({author: row.author, company: company, url: url})
 }

 bigquery.defineFunction(
  'extractCompany',                           // Name of the function exported to SQL
  ['author', 'text', 'id'],                    // Names of input columns
  [{'name': 'author', 'type': 'string'},  // Output schema
   {'name': 'company', 'type': 'string'},
  {'name': 'url', 'type': 'string'}],
  extractCompanyExample                       // Reference to JavaScript UDF
 );
diff --git a/4-top-100-companies.csv b/4-top-100-companies.csv
diff --git a/5-top-companies.rb b/5-top-companies.rb
 filename = "1-companies-and-authors-on-hacker-news.csv"

 data = File.read(filename).split("\n").map { |l| l.split(',') }
 data = data.uniq { |l| l[1] }
 File.write("top-100-companies.csv", "Company, Number of people, Usernames\n" + data.group_by { |l| l[0] }.map(&:last).sort_by(&:size).reverse.map { |l| [l.first.first, l.size, l.map { |a| a[1] }.join(" ")].join(",") }.first(100).join("\n"))
	SELECT
	company,
	author,
	url
	FROM
	extractCompany(
	SELECT
	author,
	text,
	id
	FROM
	[fh-bigquery:hackernews.comments]
	WHERE
	text LIKE "%I work at %")
	WHERE
	LENGTH(company) > 3
	ORDER BY
	company
	function extractCompanyExample(row, emit) {
	var company = (/I work at ([a-zA-Z]+)/.exec(row.text) \|\| [])[1];
	var url = "https://news.ycombinator.com/item?id=" + row.id.toString();
	emit({author: row.author, company: company, url: url})
	}

	bigquery.defineFunction(
	'extractCompany', // Name of the function exported to SQL
	['author', 'text', 'id'], // Names of input columns
	[{'name': 'author', 'type': 'string'}, // Output schema
	{'name': 'company', 'type': 'string'},
	{'name': 'url', 'type': 'string'}],
	extractCompanyExample // Reference to JavaScript UDF
	);
Company	Number of people	Usernames
Google	134	jpatokal johnmu nappy-doo bonafidehan throwaway1675 toleavetheman cromwellian boulos Filligree raldi cletus lupin_sansei TheIronYuppie archangel_one pierrefar nostrademons pfedor huggah jholman dpifke daave asuffield miles932 mgcross Nitramp btilly ramanujan chrsmith haberman dspeyer mc32 floitsch argumentum pathikrit mvgoogler srj sethladd jmillikin enneff capkutay mrb jedc j_col zaphar untouchable robrenaud alec ot akkartik DannyBee arkem petercooper pthatcherg Chronos eljaco cmcluck joshu sanj asmdsr bishnu GNUgler cbhl legohead dekhn mayanksinghal litewulf schoudha jrockway elastoplast vinkelhake xienze dmitrygr QuercusMax dragonwriter cbr johnbatch twilsndfdf olegrog rthprog tedunangst Lewisham spankalee packetslave lawnchair_larry gmurphy habosa danans esquid2 Dewie cdibona clawr baby cousin_it blinks nbarry valas aniket_ray cloudwalking TaylorAlexander joeburke fizixer twiceaday hbien larrys stuartmscott lacker zellyn hurstdog Sesse_ privacypolicy MaysonL j_baker tytso powera googlethrowaway jsolson OGinparadise smattiso Hannan jacques_chester cgs1019 ceras wuster ErrantX mhansen jakevoytko yetanotherphd rozim cmrdporcupine jastanton anatoly jemfinch rictic LPCRoy
home	71	dsleno bifrost Aaronneyer Sander_Marechal gmays dugmartin stevewillows phamilton JeffL mod robflynn phektus backwardm VLM mark_l_watson jophde dmackerman zedshaw cel1ne chrisbennet matwood SoftwareMaven egypturnash kisstheblade famousactress TheRealDunkirk toadi city41 ishbits makecheck mattdeboard jmspring forensic joelhooks scrollaway apowell nasmorn hypertexthero tortilla outside1234 bnycum vinayan3 paufernandez PierreA davidw deweller bstewartny corresation jarin bradleydwyer bodegajed bane gte910h nwatson nosuchthing PaulHoule ghaff mikekchar ikarous thom fishtoaster code_duck rabarbers delano Hrundi White_N_Nerdy tjradcliffe Timothee icey tluyben2 csomar
Microsoft	31	cgravill steve-howard sgk284 Smudge AdamTReineke CurtHagenlocher dpark DaveVoyles Elepsis sebastienros jswinghammer rowill smortaz seanmcdirmid sbilstein ephermata ScottWhigham caryme paulbatum jebblue BrandonWatson jjcm ossreality aabdocker qq66 robwilliams DinoV snprbob86 cesarbs nojvek Splines
Facebook	29	nbm strager kmavm mkjones yariv alexgartrell chrisblizzard eugenez indigoviolet dan15 douglasp chalst CGamesPlay sweis white_knight chrislong_ kozikow jasonss andralex correctbattery strlen blakeross sriramk underwater francip 31reasons orijing ashu scottmac
Mozilla	23	zpao cpeterso gkoberger lmorchard Yoric 6a68 padenot lxt briansmith pcwalton javaun shaver sayrer hamilton larsberg jlongster kumar303 fwenzel ianb potch bzbarsky dmarcos floatingatoll
Stripe	22	cristinacordova krithix apexauk pc hazelcough mlm gdb kitchenette wilfra johnjwang amfeng lachyg michaelschade thairu anurag pchristensen cca collision siddarthcs avibryant zt jhoon
Amazon	17	vaidhy amznawsthrow TruthPrevails rahimnathwani vincente hn_throw_away kome amznthrowaway21 webwright amzn_thrwy_158 timfrietas throwaway5060 jordw ekidd justinv rifung swwu
uses	9	acrooks steve8918 justin_vanw MichaelBurge snuze greggraham andrew1 foobarian nilkn
Rackspace	9	russell_h pandemicsyn fmisle bretpiatt RackerShagz cnouri wilbanksmatt thedude75 kingkilr
Twilio	9	danielzarick reneighbor kevinburke brettinlj dmor stanleydrew hox danielle17 conroy
Apple	9	seiji SCdF bubbleRefuge dropkick rlu jfb lojack patrickk zak_mc_kracken
Yahoo	8	seldo hobonumber1 tirrellp drgath i5ao perryh2 richtaur reid
does	8	ollybee DanAndersen ripter gavanwoolery DigitalSea genbattle JeffJenkins mathewsimonton
least	7	beermann terryf wenbert mnem opendais GrandMasterBirt AndyNemmity
Cloudera	7	cmccabe eclark Littleme monstrado jackowayed esammer gchanan
Twitter	6	ropiku necolas pc86 x5315 zerr Gabriel439
Balanced	6	steveklabnik msherry jareau mtamizi rmanisha zende
night	6	bilban trussi some1else snitko avalaunch Komi
such	5	isuraed spking contingencies madpickle colordrops
Netflix	5	trunnell jedberg relix42 brendangregg male_salmon
Heroku	5	thinkbohemian theandym friism _raul bgentry
Braintree	5	jtdowney michaelfairley benmills philipsflat mvboeke
NASA	5	rexpi0 maaku thearn4 psycovic23 rhino42
Trello	4	hamidpalo thedufer startupstella loumf
this	4	wyc towelguy vl ACow_Adonis
Booking	4	dkuntz2 sid6376 avar hellweaver666
Meteor	4	djmashko2 avital imslavko dgreensp
GitHub	4	rtomayko jbarnette kneath johndbritton
facebook	4	yid lbrandy collingreene MarkPNeyer
Atlassian	4	i386 Carlfish caspar jsimons
Joyent	4	bcantrill sh1mmer dap mcavage
what	4	stephengillie lloeki curun1r sombremesa
Shopify	4	allsop8184 orenmazor xal bking
Intel	4	imaffett hardwaresofton mstermagoo williadc
CloudFlare	4	rdl pencoyd buro9 xxdesmus
Quantopian	3	jbredeche fawce dunster
Oracle	3	chrisseaton sfphotoarts suyash
Cloudkick	3	tripngroove pquerna AngryParsley
Cloudant	3	mbroberg strmpnk gadamc
Sift	3	brandonb divad12 dougb5
Stack	3	jaydles sklivvz1971 dsr_
Canonical	3	fader benji-york jcastro
that	3	japhyr owyn Buttons840
dotCloud	3	shykes gabrielgrant jpetazzo
Factual	3	aphyr lpolovets donall
Basho	3	siculars bonzoesc rzezeski
MSFT	3	marshray antics felixrieseberg
Localytics	3	bsuthoff eropple calinet6
Kaggle	3	moserware numlocked antgoldbloom
Automattic	3	TheCoreh evansolomon hew
GoCardless	3	kitbrennan nqureshi timrogers
GoDaddy	3	Osiris mierle diek
VMware	3	bitops frostmatthew antirez
would	3	dirtyhand monkey_slap Legion
Yelp	3	wting pessimizer bkbleikamp
another	3	sp_ acuity12 iigs
oDesk	3	alexiaodesk john_horton pbh
blekko	3	guimarin greglindahl krishna2
starbucks	2	sliverstorm lsc
IFTTT	2	johns charliepark
Iron	2	edsrzf stephenitis
GoPro	2	gokhan brosky117
GoPollGo	2	samgro abcd_f
provides	2	jqueryin vidarh
Keen	2	mwetzler dorkitude
Khan	2	spicyj jeresig
Last	2	nervechannel dsingleton
LinkedIn	2	pscsbs joshhart
FreshBooks	2	mark_story sunir
Forbes	2	smcnally taylorbuley
Mailgun	2	twakefield old-gregg
McDonald	2	learc83 downer94
Famo	2	adamcmiel thealphanerd
Meetup	2	sighup YuriNiyazov
Etsy	2	donohoe gfodor
just	2	harry willvarfar
isocket	2	jasonshen alabut
Engine	2	EamonLeonard yerhot
Moat	2	temuze somegut
Dwolla	2	shanereiser capablemonkey
small	2	MauriceFlanagan mateuszf
imgix	2	skuhn dinasaurusrex
iRobot	2	svec tocomment
Dropbox	2	smarx ivankirigin
NVIDIA	2	jwise0 tmurray
Docracy	2	pents90 matt2000
Nokia	2	sirkneeland DavidMcLaughlin
Disney	2	musername bdkoepke
Palantir	2	MattLaroche slashclee
PayPal	2	zabraxias trg2
full	2	jenius 300bps
PayScale	2	adamb0mb1 alexzoltano
foursquare	2	nsanch jorgeortiz85
Phusion	2	tinco FooBarWidget
Pinterest	2	dfrankow folz
Pivotal	2	tedsuo sweetleon
Posit	2	hmahncke lutusp
Digg	2	cduruk lethain
Priceonomics	2	rohin omarish
CropUp	2	sulldc robgaafar
	filename = "1-companies-and-authors-on-hacker-news.csv"

	data = File.read(filename).split("\n").map { \|l\| l.split(',') }
	data = data.uniq { \|l\| l[1] }
	File.write("top-100-companies.csv", "Company, Number of people, Usernames\n" + data.group_by { \|l\| l[0] }.map(&:last).sort_by(&:size).reverse.map { \|l\| [l.first.first, l.size, l.map { \|a\| a[1] }.join(" ")].join(",") }.first(100).join("\n"))