--Отбираю даты и названия фондов, участвующих в эти даты
WITH
month_fund AS (
SELECT
dt,
COUNT(dt) fund_count
FROM (SELECT
EXTRACT(MONTH FROM funded_at::DATE) dt,
f.name
FROM funding_round fr
JOIN investment i ON fr.id=i.funding_round_id
JOIN fund f ON f.id=i.fund_id
WHERE (funded_at BETWEEN '2010-01-01' AND '2013-12-31')
AND f.country_code='USA'
GROUP BY EXTRACT(MONTH FROM funded_at::DATE), f.name) AS a
GROUP BY dt),
month_acquired AS (
SELECT
COUNT(*) count_sold_companies,
dt,
SUM(total) total
FROM (
SELECT
EXTRACT(MONTH FROM acquired_at::DATE) AS dt,
price_amount total,
acquired_company_id
FROM acquisition a
JOIN company c ON c.id=a.acquired_company_id
WHERE acquired_at BETWEEN '2010-01-01' AND '2013-12-31'
GROUP BY EXTRACT(MONTH FROM acquired_at::DATE), price_amount, acquired_company_id
ORDER BY EXTRACT(MONTH FROM acquired_at::DATE)) AS b
GROUP BY dt)
--Объединяю таблицы про фонды и проданные компании
SELECT month_acquired.dt, fund_count, count_sold_companies, total
FROM month_fund
LEFT JOIN month_acquired ON month_acquired.dt=month_fund.dt
ORDER BY dt