- WITH
- f AS (SELECT name,
- id
- FROM fund
- WHERE country_code = 'USA'),
- a AS (SELECT acquired_company_id,
- price_amount,
- acquired_at
- FROM acquisition)
- SELECT EXTRACT(MONTH FROM fr.funded_at) AS month,
- COUNT(DISTINCT f.name) AS fund_count,
- COUNT(DISTINCT a.acquired_company_id) AS company_count,
- SUM(a.price_amount) AS amount
- FROM funding_round AS fr
- LEFT JOIN investment AS i ON fr.id = i.funding_round_id
- LEFT JOIN f ON f.id = i.fund_id
- LEFT JOIN company AS c ON c.id = fr.company_id
- LEFT JOIN a ON a.acquired_company_id = c.id
- WHERE EXTRACT(YEAR FROM fr.funded_at) BETWEEN 2010 AND 2013
- AND fr.raised_amount > 0
- GROUP BY month