WITH /*d AS (SELECT CAST(DATE_TRUNC ('month', funded_at) AS date) AS dm,
           id AS id_d
FROM funding_round
WHERE EXTRACT(YEAR FROM funded_at) BETWEEN 2010 AND 2013 ),

f AS (SELECT DISTINCT name AS name_f,
      id AS id_f
FROM fund
WHERE country_code = 'USA'),

top AS (SELECT*,inv.id AS inv_id
FROM f
JOIN investment AS inv ON f.id_f=inv.fund_id
JOIN d ON d.id_d=inv.funding_round_id
JOIN company AS co ON co.id= inv.company_id
JOIN acquisition AS ac ON ac.acquired_company_id=co.id),
*/
name_sale AS (
SELECT name AS name_sal,
    country_code AS contry_sale,
    acquired_company_id AS id_sale,
    ROUND(price_amount) AS sum_sale,
    acquired_at AS date_sale
FROM company AS cc
RIGHT JOIN acquisition AS ac ON cc.id=ac.acquired_company_id
WHERE price_amount > 0),

round_sale AS (
SELECT fr.id AS id_round,
       fr.company_id AS id_company,
       fr.funded_at AS date_round,
       iv.fund_id
FROM funding_round AS fr
JOIN investment AS iv ON fr.id=iv.funding_round_id
WHERE fr.funded_at IS NOT NULL
AND EXTRACT(YEAR FROM funded_at) BETWEEN 2010 AND 2013 ),

contry_fund AS (
SELECT id_round,
       id_company,
       date_round,
       fund_id,
       name AS fund_name,
       country_code AS fund_country
FROM round_sale AS rs
JOIN fund AS f ON rs.fund_id=f.id
WHERE investment_rounds > 0
    AND invested_companies >0
    AND country_code = 'USA'
    AND EXTRACT(YEAR FROM date_round) BETWEEN 2010 AND 2013
)
SELECT EXTRACT(MONTH FROM date_round) AS month_round,
        COUNT(DISTINCT acquired_company_id) AS count_company_sale,
       COUNT(DISTINCT fund_id) AS name_fund ,

       SUM(price_amount) AS total_sum
FROM contry_fund AS cff
JOIN company AS cc ON cc.id=cff.id_company
JOIN acquisition AS ac ON cc.id=ac.acquired_company_id
WHERE price_amount>0
AND acquired_company_id IS NOT NULL
AND EXTRACT(YEAR FROM date_round) BETWEEN 2010 AND 2013
AND fund_country = 'USA'
--AND EXTRACT(YEAR FROM acquired_at) BETWEEN 2010 AND 2013
GROUP BY EXTRACT(MONTH FROM date_round)



/*s_12 AS (SELECT EXTRACT(MONTH FROM dm) AS month,
       COUNT(DISTINCT name_f) AS name_fond ,
       COUNT( acquired_company_id) AS coun_company,
        SUM(price_amount) AS total_sum
FROM top
WHERE price_amount>0
GROUP BY EXTRACT(MONTH FROM dm))*/


/*SELECT EXTRACT(MONTH FROM date_round) AS month_round,
       COUNT(DISTINCT fund_id) AS name_fund ,
       COUNT( date_sale) AS count_company_sale,
       SUM(sum_sale) AS total_sum
FROM contry_fund AS cff
FULL JOIN name_sale AS nss ON nss.id_sale=cff.id_company
WHERE sum_sale>0
AND EXTRACT(YEAR FROM date_round) BETWEEN 2010 AND 2013
AND fund_country = 'USA'
GROUP BY EXTRACT(MONTH FROM date_round)*/