WITH
--Отбираю даты и названия фондов, участвующих в эти даты
FIRST AS (
SELECT
DATE_TRUNC('month',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 DATE_TRUNC('month',funded_at)::DATE, f.name),
--Считаю количество уникальных фондов по этим датам
SECOND AS (
SELECT
dt,
COUNT(dt) fund_count
FROM FIRST
GROUP BY dt),
--Отбираю даты, названия проданных компаний и сумму продажи
third AS (
SELECT
acquired_company_id,
DATE_TRUNC('month',acquired_at)::DATE AS dt,
price_amount total
FROM acquisition a
WHERE acquired_at BETWEEN '2010-01-01' AND '2013-12-31'
GROUP BY acquired_company_id, DATE_TRUNC('month',acquired_at)::DATE, price_amount
ORDER BY DATE_TRUNC('month',acquired_at)::DATE),
--Считаю уникальные названия проданных компаний, даты и сумму продажи
forth AS (
SELECT
COUNT(dt) count_sold_companies,
dt,
SUM(total) total
FROM third
GROUP BY dt)
--Объединяю таблицы про фонды и проданные компании
SELECT forth.dt, count_sold_companies, total, fund_count
FROM forth
JOIN SECOND ON forth.dt=SECOND.dt
ORDER BY dt