gistfile1.txt

burp:cbase jd$ cat by_age_at_exit.sql | psql -h localhost crunchbase
 years_old_at_exit | companies | sum_money_raised | sum_market_cap | ipos | sum_cash_acq_value | cash_acqs | sum_other_acq_value | other_acqs | deadpooled
-------------------+-----------+------------------+----------------+------+--------------------+-----------+---------------------+------------+------------
                   |     46890 |      52668785410 |    47428700000 |   15 |              36000 |        11 |                     |         20 |       1033
                 0 |        98 |        300371000 |     2413700000 |    3 |            5069359 |        15 |           164230000 |         80 |          0
                 1 |       240 |        419549701 |                |    0 |        13799600000 |        34 |          3978166000 |        206 |          4
                 2 |       261 |       2169269725 |     8387000000 |    4 |          121287000 |        24 |          6519760000 |        233 |          4
                 3 |       182 |       6803328345 |     4859000000 |    3 |         2011950000 |        17 |          2244300000 |        163 |          1
                 4 |       161 |       1851429353 |     4526000000 |    3 |         1090000000 |        15 |          2929700000 |        143 |          1
                 5 |       114 |       2601711898 |    12860500000 |    3 |         1637500000 |        16 |          5629500000 |         95 |          2
                 6 |        93 |       2726139285 |    15344600000 |   11 |          781800000 |        12 |          4507000000 |         73 |          1
                 7 |        57 |       2415030776 |    44634900000 |    6 |          659400000 |         9 |           850500000 |         42 |          0
                 8 |        33 |       3349210580 |   149164300000 |    5 |           59000000 |         1 |           249500000 |         28 |          0
                 9 |         7 |        137180000 |                |    0 |          822500000 |         3 |           400000000 |          4 |          0
                10 |         1 |         15000000 |                |    1 |                    |         0 |                     |          0 |          0
(12 rows)

burp:cbase jd$ cat by_age_at_exit.sql
-- Source: Crunchbase.com & Yahoo Finance
select
    case
      when ipo_date > founded then (ipo_date - founded) / 365
      when acquisition_date > founded then (acquisition_date - founded) / 365
    end as years_old_at_exit,
    count(*) as companies,
    sum(money_raised) as sum_money_raised,
    sum(ycv.market_cap) as sum_market_cap,
    count(distinct companies.stock_symbol) as ipos,
    sum(case when acquisition_type = 'cash' then acquisition_valuation end) as sum_cash_acq_value,
    count(distinct case when acquisition_type = 'cash' then slug end) as cash_acqs,
    sum(case when acquisition_type is null or acquisition_type <> 'cash' then acquisition_valuation end) as sum_other_acq_value,
    count(distinct case when (acquisition_type is null and (acquisition_valuation is not null or acquisition_date is not null)) or acquisition_type <> 'cash' then slug end) as other_acqs,
    count(distinct case when deadpooled is not null then slug end) as deadpooled
from
    companies
left join
    yahoo_current_values ycv on regexp_replace(companies.stock_symbol, E'[\\w\\s]+:\\s?', '') = ycv.stock_symbol
where founded >= '2003-01-01'::date
group by 1
order by 1 nulls first;