Project Task 22

WITH

a AS (SELECT  COUNT(DISTINCT(f.id)) AS number_of_funds,
              EXTRACT(MONTH FROM CAST(founded_at AS date)) AS month
      FROM fund AS f
          LEFT JOIN investment AS i ON f.id = i.fund_id
          LEFT JOIN funding_round AS fr ON i.funding_round_id = fr.id
      WHERE f.country_code = 'USA'
          AND EXTRACT(YEAR FROM CAST(fr.funded_at AS date)) BETWEEN 2010 AND 2013
      GROUP BY month
    ),

b AS (SELECT  COUNT(acquired_company_id) AS purchases,
              SUM(price_amount) AS total_turnover,
              EXTRACT(MONTH FROM CAST(acquired_at AS date)) AS month
      FROM acquisition
      WHERE EXTRACT(YEAR FROM CAST(acquired_at AS date)) BETWEEN 2010 AND 2013
      GROUP BY month
    )

SELECT  a.month,
        a.number_of_funds,
        b.purchases,
        b.total_turnover
FROM a
JOIN b ON a.month = b.month
ORDER BY a.month
;