gistfile1.txt

burp:cbase jd$ cat by_rounds.sql | psql -h localhost crunchbase
 round | companies | sum_money_raised | sum_market_cap | ipos | sum_cash_acq_value | cash_acqs | sum_other_acq_value | other_acqs | deadpooled
-------+-----------+------------------+----------------+------+--------------------+-----------+---------------------+------------+------------
       |     45144 |      16213123413 |    49167200000 |   20 |        14328067359 |       106 |          7518786000 |        715 |        888
 a     |      1707 |       9787128569 |     1247500000 |    4 |         1074625000 |        26 |          2260300000 |        184 |        111
 b     |       741 |      13561932416 |    12151200000 |    6 |         1407500000 |        11 |          8899570000 |        104 |         30
 c     |       333 |      14202170248 |    17234900000 |    6 |         2190950000 |        10 |          4501500000 |         54 |         12
 d     |       145 |      13522874735 |   134027000000 |    9 |          668000000 |         2 |          3630500000 |         24 |          4
 e     |        50 |       5258030817 |    32411000000 |    4 |         1319000000 |         2 |           662000000 |          5 |          1
 f     |        13 |       1577414744 |    19108000000 |    3 |                    |         0 |                     |          1 |          0
 g     |         4 |       1334331131 |    24271900000 |    2 |                    |         0 |                     |          0 |          0
(8 rows)

burp:cbase jd$ cat by_rounds.sql
-- Source: Crunchbase.com & Yahoo Finance
with
company_rounds as (
    select
      slug as round_slug,
      max(case when length(round_code) = 1 then round_code else null end) as max_round
    from
        companies left join company_funding on companies.slug = company_slug
    group by 1
)

select
    max_round as round,
    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
inner join
    company_rounds on round_slug = slug
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;