gistfile1.txt

crunchbase=# -- Source: Crunchbase.com & Yahoo Finance
crunchbase=# with money_buckets as (
crunchbase(#     select 0 as bucket
crunchbase(#
crunchbase(#     union all
crunchbase(#
crunchbase(#     select
crunchbase(#         distinct pow(10, floor(log(money_raised)/log(10))) as bucket
crunchbase(#     from
crunchbase(#         companies
crunchbase(#     where
crunchbase(#         founded >= '2003-01-01'::date and money_raised > 0
crunchbase(# )
crunchbase-# select
crunchbase-#     money_buckets.bucket as money_raised,
crunchbase-#     count(*) as companies,
crunchbase-#     sum(money_raised) as sum_money_raised,
crunchbase-#     sum(ycv.market_cap) as sum_market_cap,
crunchbase-#     count(distinct companies.stock_symbol) as ipos,
crunchbase-#     sum(case when acquisition_type = 'cash' then acquisition_valuation end) as sum_cash_acq_value,
crunchbase-#     count(distinct case when acquisition_type = 'cash' then slug end) as cash_acqs,
crunchbase-#     sum(case when acquisition_type is null or acquisition_type <> 'cash' then acquisition_valuation end) as sum_other_acq_value,
crunchbase-#     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,
crunchbase-#     count(distinct case when deadpooled is not null then slug end) as deadpooled
crunchbase-# from
crunchbase-#     money_buckets
crunchbase-# inner join
crunchbase-#     companies on case when money_raised > 0 then pow(10, floor(log(money_raised)/log(10))) else 0 end = money_buckets.bucket
crunchbase-# left join
crunchbase-#     yahoo_current_values ycv on regexp_replace(companies.stock_symbol, E'[\\w\\s]+:\\s?', '') = ycv.stock_symbol
crunchbase-# where founded >= '2003-01-01'::date
crunchbase-# group by 1
crunchbase-# order by 1;
 money_raised | companies | sum_money_raised | sum_market_cap | ipos | sum_cash_acq_value | cash_acqs | sum_other_acq_value | other_acqs | deadpooled
--------------+-----------+------------------+----------------+------+--------------------+-----------+---------------------+------------+------------
            0 |     40409 |                0 |    41122200000 |   10 |        14503167359 |        99 |          5502751000 |        553 |        441
         1000 |        92 |           403766 |                |    0 |                    |         0 |                     |          0 |         19
        10000 |      1114 |         41940530 |                |    0 |                    |         0 |                     |         14 |        142
       100000 |      2451 |        920360065 |                |    0 |            8500000 |         7 |           105235000 |         85 |        226
      1000000 |      2812 |       9301408021 |     4816500000 |    6 |          646975000 |        32 |          2132900000 |        275 |        173
     10000000 |      1152 |      33352352104 |    19742800000 |   17 |         4629500000 |        18 |         17174270000 |        152 |         44
    100000000 |       103 |      21407386660 |    72530200000 |   17 |         1200000000 |         1 |          2557500000 |          7 |          1
   1000000000 |         4 |      10433154927 |   151407000000 |    4 |                    |         0 |                     |          1 |          0
(8 rows)