Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
800 B
1
Indexable
Never
WITH fir AS
  (SELECT EXTRACT(MONTH FROM CAST(fr.funded_at AS timestamp)) AS month,
   COUNT(DISTINCT f.name)
   FROM company AS comp
   LEFT JOIN funding_round AS fr ON comp.id=fr.company_id
   LEFT JOIN investment AS inv ON comp.id = inv.company_id
   LEFT JOIN fund AS f ON inv.fund_id = f.id
   WHERE EXTRACT(YEAR
                 FROM CAST(fr.funded_at AS timestamp)) BETWEEN 2010 AND 2013
     AND comp.country_code = 'USA'
   group by month),
     sec AS
  (SELECT EXTRACT(MONTH FROM CAST(acquired_at AS timestamp)) AS month,
            COUNT(acquired_company_id),
             sum(price_amount)
     FROM acquisition
  group by month)
     
SELECT fir.month, fir.count, sec.count,sec.sum
       
FROM fir 
LEFT OUTER JOIN sec ON fir.month=sec.month
order by fir.month